Postgres: complex CASCADE question - making sure you only delete unique foreign key references?

Question

I've got some linked tables in a Postgres database, as follows:

    Table "public.key"
 Column | Type | Modifiers 
--------+------+-----------
 id     | text | not null
 name   | text | 
Referenced by:
    TABLE "enumeration_value" CONSTRAINT "enumeration_value_key_id_fkey" FOREIGN KEY (key_id) REFERENCES key(id)

Table "public.enumeration_value"
 Column | Type | Modifiers 
--------+------+-----------
 id     | text | not null
 key_id | text | 
Foreign-key constraints:
    "enumeration_value_key_id_fkey" FOREIGN KEY (key_id) REFERENCES key(id)
Referenced by:
    TABLE "classification_item" CONSTRAINT "classification_item_value_id_fkey" FOREIGN KEY (value_id) REFERENCES enumeration_value(id)

Table "public.classification_item"
     Column     | Type | Modifiers 
----------------+------+-----------
 id             | text | not null
 transaction_id | text | 
 value_id       | text | 
Foreign-key constraints:
    "classification_item_transaction_id_fkey" FOREIGN KEY (transaction_id) REFERENCES transaction(id)
    "classification_item_value_id_fkey" FOREIGN KEY (value_id) REFERENCES enumeration_value(id)

I want to

• delete all classification_items associated with a certain transaction
• delete all enumeration_values associated with those classification_items
• and finally, delete all key items associated with those enumeration_values.

The difficulty is that the key items are NOT unique to enumeration_values associated (via classification_item) with a certain transaction. They get created independently, and can exist across multiple of these transactions.

So I know how to do the second two of these steps, but not the first one:

delete from key where id in (select key_id from enumeration_value where id in (select value_id from "classification_item" where id = (select id from "transaction" where slice_id = (select id from slice where name = 'barnet')))); 
# In statement above: help! How do I make sure these keys are ONLY used with these values?
delete from enumeration_value where id in (select value_id from "classification_item" where id = (select id from "transaction" where slice_id = (select id from slice where name = 'barnet')));
delete from classification_item where transaction_id in (select id from "transaction" where slice_id = (select id from slice where name = 'barnet'));

If only postgres had a CASCADE DELETE statement....

Answer 1

If only postgres had a CASCADE DELETE statement....

PostgreSQL has this option for a long time, as of version 8.0 (5 years ago). Just use them.