Postgresql – How to find out if rows are refenced from other tables

foreign keyjoin;postgresqlunion

I have a table named contacts (primary key id) which is referenced by a number of other tables via foreign keys. When a list of contacts is displayed in the application, I get their primary keys in an array and need to determine if these records could be deleted. This is just used for display, no actual deleting is taking place here.

Based on this answer, I can find out if one record can be deleted:

(SELECT 1 FROM case_contacts WHERE contact_id = 5000 LIMIT 1)
UNION ALL
(SELECT 1 FROM case_payments WHERE contact_id = 5000 LIMIT 1)
UNION ALL
(SELECT 1 FROM invoices WHERE contact_id = 5000 LIMIT 1)
-- etc
LIMIT 1;

I could run this query for every contact ID I'm given, but that seems very inefficient.

Is there a way to send a single query for multiple contact IDs (up to ~500 at once)? Any kind of result would be fine, including only the IDs that can be deleted, or only the IDs that cannot be deleted, or a result set of (contact_id, can_be_deleted).

EDIT: This is what I'm using until I find a more efficient solution:

SELECT  c.id
  FROM  contacts c
 WHERE  c.id IN (1,10,20,1557,5000,15057)
        AND NOT EXISTS (SELECT 1 FROM case_contacts WHERE contact_id = c.id)
        AND NOT EXISTS (SELECT 1 FROM case_payments WHERE contact_id = c.id)
        AND NOT EXISTS (SELECT 1 FROM invoices      WHERE contact_id = c.id);

Best Answer

You may need to alter this slightly for postgres (is is MS SQL syntax) but something like:

SELECT id
FROM   contacts
LEFT OUTER JOIN FROM case_contacts ON case_contacts.contact_id = contacts.id
LEFT OUTER JOIN FROM case_payments ON case_payments.contact_id = contacts.id
LEFT OUTER JOIN FROM invoices      ON      invoices.contact_id = contacts.id
WHERE case_contacts.contact_id IS NULL
AND   case_payments.contact_id IS NULL
AND        invoices.contact_id IS NULL

or:

SELECT id
FROM   contacts
WHERE  id NOT IN (SELECT contact_id FROM case_contacts)
AND    id NOT IN (SELECT contact_id FROM case_payments)
AND    id NOT IN (SELECT contact_id FROM invoices)

should list the IDs that are not referred to in those three tables. The query planner *should * see these as equivalent and optimise accordingly, but if not then the former will be more efficient (the latter risks running the three sub-queries once for every row in contacts).

Related Solutions

Efficiency for contact-contact relationships schema

Since you invoked The One True Way... I'll invoke it. 1NF would insist on "no repeating groups," which is what cid_a and cid_b are... two columns of the same "stuff" (to use the technical term).

You should not have to look at data two different ways to get the correct answer.

contact           relationship              contact_relationship_map
--------          -------------             ----------------------
cid (PK)          relationship_id (PK)      relationship_id (FK) \\ P   
name              details                   cid (FK)             // K
                  start_date                + INDEX(cid,relationship_id)
                  end_date
                  relationship_type

Each relationship gets a record in relationship, which has an ID, which is used to insert two rows into contact_relationship_map -- one for each peer in the relationship.

The PK of this table is both columns combined, and it should be indexed on both columns combined in the opposite order so that searching by relationship_id or cid has the benefit of the index. The latter index doesn't need to be declared as unique because the primary key will enforce that. Neither column allows nulls and deletes from the parent tables cascade to the records of this table.

To find relationships starting with a name from 'contact' and a relationship_type of = 'friend' we look up starting in c1:

SELECT c2.cid as my_friends_cid, c2.name as my_friends_name 
  FROM contact c1
  JOIN contact_relationship_map crm1 on crm1.cid = c1.cid
  JOIN relationship r on r.id = crm1.relationship_id 
  JOIN contact_relationship_map crm2 on crm2.relationship_id = crm1.relationship_id
                                    and crm2.cid != c1.cid
  JOIN contact c2 on c2.cid = crm2.cid
 WHERE c1.name = 'first_contact_name_here'
   AND r.relationship_type = 'friend';

In other words, following:

c1 -> crm1 -> crm2 -> c2
          \-> r

All of these joins are easily satisifed by indexes so the number of joins here should not be any cause for concern.

If you already know the cid from the first contact, that table can be eliminated from the query, and you'd start with WHERE crm1.cid = ?

This also opens up the possibility of relationships with more than two peers, if you ever wanted it.

Postgresql – Best way of finding rows referencing a given id on PostgreSQL

I suggest your first option, with two improvements and some simplifications.

(
SELECT 1      -- irrelevant what you select here
FROM   client_category_price
WHERE  sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460'
LIMIT  1      -- may be redundant
)
UNION ALL     -- not just UNION

  ...

UNION ALL
(
SELECT 1
FROM   work_order_item
WHERE  sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460'
LIMIT  1
)
LIMIT  1;      -- this one is crucial

Given that all you want to know is

if any of those (table, column) have my row's id there, which would prevent its deletion.

You don't need a full list of violating rows. Stop searching at the first one. All you need to do is add another LIMIT 1 at the end of the query. This way, Postgres skips rest of the query as soon as the first row is found. You probably don't need LIMIT 1 for each SELECT, just the one at the end. Test without, it may produce different query plans.
Use UNION ALL instead of UNION. Faster.
Some other simplifications.

Best Answer

Related Solutions

Efficiency for contact-contact relationships schema

Postgresql – Best way of finding rows referencing a given id on PostgreSQL

Related Question