1

Let's say I have Table A, which has one column id, which is unique within the table. I have multiple other tables: Table B, Table C, ..., Table N. Each of these tables has a column A_id, which is a foreign key to id of Table A.

Is there an elegant way to get all rows of Table A which are not mapped to as a foreign key by any of the other tables? Ideally, I would like to see if there's a way to do this without having to type in a complicated query that involves manually querying each other dependent table, but any approach would be appreciated.

  • I'm not sure what you mean by "Ideally, I would like to see if there's a way to do this without having to type in a complicated query that involves manually querying each other dependent table". This query is not complicated, but you will have to test each table for the absence of rows. – David Buttrick Jul 3 '18 at 19:11
1

This can be accomplished with a NOT EXISTS () clause in your WHERE clause.

SELECT id
FROM A
WHERE NOT EXISTS (
SELECT id
FROM B
WHERE A.ID = B.A_id
)
AND NOT EXISTS (
SELECT id
FROM C
WHERE A.ID = C.A_id)
...
...

You could generate this query programmatically by querying the schema for tables that have a foreign key on A.id, and then iterating over them to create the WHERE clause...

  • Got it, so does psql not internally keep track of these references? I.e., is there no way to make single query, so that if I add new tables (which depend on A_id), I don't have to modify this query as well? I'm not too sure what you mean by "query programmatically". – stoneman_41 Jul 3 '18 at 21:09
  • The database does not know what your intent is. It knows the relationships that exist - a table has a foreign key on a column in a different table. But it does not understand your rule that table A constraints on all the other tables are mandatory in the query. I would call this a business rule. You'll always have to modify this query, as you add more tables. This is why I recommend letting your software generate the query - that way you can add more tables with this relation, and your software will "do the right thing(tm)" automatically. – David Buttrick Jul 4 '18 at 17:32
  • Okay thanks. My reasoning was that I wanted to use one database as a "resource", and I ideally didn't want to have to type in a new table every time I create one (some bugs can easily come out of this if I or someone else forgets to do this). – stoneman_41 Jul 5 '18 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.