How to know if a foreign key has cascade on delete clause

Due to a misconception of the postgresql database I work with everyday, on the 155 tables that the schema of the database contains, some tables with foreign key doesn’t have the ‘on delete cascade‘ clause wich cause serious trouble as you can expect.

I would like to correct this and with PGAdmin I’m able to see all FK of a table but not if the FK as the clause. Instead of, for each tables, dropping manually each FK and creating new ones with the ‘on delete cascade’, I would like to know how, for a specific table, the full definition of the foreign keys. Like this I will not drop foreign keys that already has the clause.

Thanks in advance !

Answer

You can look in pg_constraint:

postgres=# create table car (id int primary key, name text);
CREATE TABLE
postgres=# create table driver (id int, car int references car(id) on delete cascade);
CREATE TABLE
postgres=# select connamespace, conname, c.relname as child_table, p.relname as parent_table, confdeltype 
from pg_constraint 
join pg_class c on c.oid=conrelid 
join pg_class p on p.oid=confrelid;
 connamespace |     conname     | child_table | parent_table | confdeltype 
--------------+-----------------+-------------+--------------+-------------
         2200 | driver_car_fkey | driver      | car          | c
(1 row)

This will show you all the foreign keys (along with their source and target tables) that do not have ON DELETE CASCADE:

select connamespace, conname, c.relname as child_table, p.relname as parent_table, confdeltype
from pg_constraint 
join pg_class c on c.oid=conrelid 
join pg_class p on p.oid=confrelid
where confdeltype <> 'c';

More information in the documentation

Disclosure: I work for EnterpriseDB (EDB)