[SOLVED] Does Postgres support 'ON DELETE CASCADE' with composite foreign Key

Does Postgres support 'ON DELETE CASCADE' with composite foreign Key

This is how my tables look like

CREATE TABLE T1(
id uuid not null,
order_time timestamptz NOT NULL,
PRIMARY KEY (id, order_time),
)

CREATE TABLE T2(
id uuid not null PRIMARY KEY,
t1_id uuid not null,
t1_order_time timestamptz not null,
FOREIGN KEY (t1_id, t1_order_time) REFERENCES T1 (id, order_time) ON DELETE CASCADE
)

I intend to partition Table T1 on id and order time hence it has composite primary key on id and order time.

If I try to drop T1, postgres as expected gives error “cannot drop table because of constraint, Hint: Use DROP ... CASCADE to drop the dependent objects too.”

Now if I do ‘drop table T1 cascade’ it drops T1 and just deletes the foreign key constraints of T2. No corresponding rows are deleted from T1

Solution

From the manual:

Automatically drop objects that depend on the table (such as views), and in turn all objects that depend on those objects

The objects are the database objects like views and constraints. Not data. If you want to remove related data, you have to use DELETE or TRUNCATE first.