0

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

2
  • You can declare the foreign keys as on delete cascade then you only need to delete from table A Aug 7, 2020 at 7:33
  • Unrelated to your problem, but: Postgres 9.2 is no longer supported you should plan an upgrade as soon as possible. Aug 7, 2020 at 7:33

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.