Postgresql – Delete rows on a table with cascading foreign keys

deleteforeign keypostgresql

I want to delete rows on Table A but it has foreign keys on Table B and Table D. And Table B has foreign keys in Table E and Table D has foreign key in table F. Can I delete all the cascading records from Table A to F in a simple script without making multiple delete scripts per table? In reality, Table A has around 20 table referencing to it.

Table A

  • Table B
    • Table E
  • Table C
  • Table D
    • Table F

PostgreSQL version: 9.2.24

Best Answer

First, it depends on how your foreign keys are declared. Assuming tables like:

CREATE TABLE parent
( pid ... not null primary key
, ...
);

CREATE TABLE child
( ...
, pid ... not null
    references parent (pid)
        on delete <action>
        on update ...
...
);

action can be any of:

  • NO ACTION Produce an error indicating that the deletion or update would create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if there still exist any referencing rows. This is the default action.

  • RESTRICT Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable.

  • CASCADE Delete any rows referencing the deleted row, or update the value of the referencing column to the new value of the referenced column, respectively.

  • SET NULL Set the referencing column(s) to null.

  • SET DEFAULT Set the referencing column(s) to their default values.

See https://www.postgresql.org/docs/9.2/sql-createtable.html

If your foreign keys are declared as "on delete cascade" it is - in theory - sufficient to delete the root node. In practice, there may be physical limitations that restrict the total number of rows that can be deleted in one transaction.

If you want to experiment with the different actions you can use Fiddle. 9.5 is the oldest one available. If you are still on 9.2, consider upgrading to something more modern.

Related Question