postgresql on delete cascade

Question

I have two tables:

TableY:     id, name, description  TableX:     id, name, y_id 

I have added a foreign key in TableX (y_id REFERENCING tableY id ON DELETE CASCADE). When I delete from TableX, the TableY id remains (while it theoritically should be deleted). I guess I have misunderstood how the ON DELETE CASCADE option works. Can anyone tell what I am doing wrong?

I saw this ON DELETE CASCADE as well but did not make much sense to me.

Answer 1

Short answer

ON DELETE CASCADE specifies constraint option. In your case (suppose you have table_x and table_y that depends on table_x) when you delete row in table_x and this row is referenced from table_y, row from table_x would be deleted and all rows that referenced this row in table_y would be deleted as well.

When you want remove table and on this table depend one or more other tables use DROP TABLE with CASCADE key word.

More about DROP, DELETE and ON DELETE CASCADE constraint option

Drop vs delete

When you want to remove table from database use word drop. When you use word delete it means that you want to remove/delete content from the table (but want the table to stay = to continue exist in database).

Deleting row that is referenced from other table

When you have row in the table (e.g. table_x and row with id 1) and this row is referenced from other table (in other table there is foreign key linked with this entry) you cannot delete the row as follow. You would get an error.

DELETE FROM table_x WHERE id = 1;  ERROR:  update or delete on table "table_x" violates foreign key constraint "constraint_name" on table "table_y" DETAIL:  Key (id)=(1) is still referenced from table "table_y". 

The reason is described in the detail part of the error. The entry/entries in other table/tables reference to this entry. As example you can imagine table account and table account_activity. When you delete entry from table account (you delete one account) you should delete all entries from table account_activity that reference to this specific entry in table account. If not you would end up with account activities that do not refer to any account.

There are two possibilities depending on what you want to achieve:

1. You want to delete content of whole table (table_x) and content of all tables that have reference (foreign key) to this table.
2. You want to delete one or more entries (rows) specified in WHERE clause from one table and also delete all rows that reference to this entry/entries (like the example with account and account_activity mentioned above).

1) Use TRUNCATE with CASCADE key word

TRUNCATE table_x CASCADE; 

2) Change constraint you have in table_y on the column to have option ON DELETE CASCADE.

ALTER TABLE table_y        DROP CONSTRAINT constraint_name,        ADD CONSTRAINT constraint_name FOREIGN KEY (column_in_table_y)           REFERENCES table_x (referenced_column_in_table_x) ON DELETE CASCADE; 

Dropping table on which other tables depend

When you have table (e.g. table_x) and other table/tables depend on it you cannot drop this table. Depending means that the other tables reference (have foreign key) to this table (table_x). When you try to drop such table you get error.

DROP TABLE table_x;  ERROR:  cannot drop table table_x because other objects depend on it DETAIL:  constraint id_x_fk on table table_y depends on table table_x HINT:  Use DROP ... CASCADE to drop the dependent objects too. 

The error gives hint. When you want drop such a table you have to use drop with a cascade key word. The table will be dropped and also all constraints that reference to this table will be dropped.

DROP TABLE table_x CASCADE; 

Examples

Creates table_x and table_y.

CREATE TABLE table_x (   id integer NOT NULL,   text character varying(255) NOT NULL,   CONSTRAINT table_x_pk PRIMARY KEY (id) );  CREATE TABLE table_y (   id integer NOT NULL,   text character varying(255) NOT NULL,   id_x integer NOT NULL,   CONSTRAINT table_y_pk PRIMARY KEY (id) ); 

Creates constraint with name id_x_fk on column id_x in table_y.

ALTER TABLE table_y   ADD CONSTRAINT id_x_fk FOREIGN KEY (id_x)       REFERENCES table_x (id); 

Inserts test data to table_x and table_y.

INSERT INTO table_x VALUES     (1, 'super x'),     (2, 'great x');  INSERT INTO table_y VALUES     (1, 'y one', 2),     (2, 'y two', 1),     (3, 'y three', 1); 

Deleting from table_x (error).

DELETE FROM table_x WHERE id = 1; 

Dropping and adding constraint with the same name.

ALTER TABLE table_y   DROP CONSTRAINT id_x_fk,   ADD CONSTRAINT id_x_fk FOREIGN KEY (id_x)       REFERENCES table_x (id) ON DELETE CASCADE; 

Deleting from table_x and also from table_y (correct).

DELETE FROM table_x WHERE id = 1; 

Deleting all content from table_x and table_y.

TRUNCATE table_x CASCADE; 

Dropping (removing) table_x and dropping id_x_fk constraint in table_y.

DROP TABLE table_x CASCADE; 

Notice

If you have row in table_x (e.g. with id = 3) and this entry (this id) is not referenced from table_y you can delete the row even though the constraint does not have ON DELETE CASCADE option (because there is no constraint violation).

The code was tested on "PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit".

Sources:

http://www.postgresql.org/docs/current/static/sql-droptable.html

http://www.postgresql.org/docs/current/static/sql-delete.html

http://www.postgresql.org/docs/current/static/ddl-constraints.html

http://www.postgresql.org/docs/current/static/sql-altertable.html

PostgreSQL delete all content

Answer 2

I guess you got misunderstanding. Try to delete row from TableY and corresponding rows from TableX will be cascade deleted. This option is indispensable when you have secondary related tables and would like to clean them all by deleting parent row from primary table without getting constraints violated or rubbish left.