Remove rows NOT referenced by a foreign key

Question

This is somewhat related to this question:

I have a table with a primary key, and I have several tables that reference that primary key (using foreign keys). I need to remove rows from that table, where the primary key isn't being referenced in any of those other tables (as well as a few other constraints).

For example:

Group
groupid | groupname
    1   |    'group 1'
    2   |    'group 3'
    3   |    'group 2'
    ... |    '...'

Table1
tableid | groupid | data
    1   |    3    |    ...
    ... |    ...  |    ...

Table2
tableid | groupid | data
    1   |    2    |    ...
    ... |    ...  |    ...

and so on. Some of the rows in Group aren't referenced in any of the tables, and I need to remove those rows. In addition to this, I need to know how to find all of the tables/rows that reference a given row in Group.

I know that I can just query every table and check the groupid's, but since they are foreign keys, I imagine that there is a better way of doing it.

This is using Postgresql 8.3 by the way.

Answer 1

DELETE
FROM    group g
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    table1 t1
        WHERE   t1.groupid = g.groupid
        UNION ALL
        SELECT  NULL
        FROM    table1 t2
        WHERE   t2.groupid = g.groupid
        UNION ALL
        …
        )

Answer 2

At the heart of it, SQL servers don't maintain 2-way info for constraints, so your only option is to do what the server would do internally if you were to delete the row: check every other table.

If (and be damn sure first) your constraints are simple checks and don't carry any "on delete cascade" type statements, you can attempt to delete everything from your group table. Any row that does delete would thus have nothing reference it. Otherwise, you're stuck with Quassnoi's answer.