Geek Answers Handbook

Delete rows that are not referenced by a foreign key

This is somewhat related to this question :

I have a table with a primary key, and I have several tables that reference this primary key (using foreign keys). I need to delete rows from this table where the primary key does not refer to any of these other tables (as well as to several other restrictions).

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    |    ...
    ... |    ...  |    ...

etc. Some rows in the group are not mentioned in any of the tables, and I need to delete these rows. In addition to this, I need to know how to find all tables / rows that refer to a given row in a group.

I know that I can simply query each table and check the group, but since they are foreign keys, I think there is a better way to do this.

, , Postgresql 8.3.

+5
2

, SQL- , - , , : .

(, ), " ", . , , , . .

+3
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
        …
        )
+10

More articles:


All Articles