Search code examples
postgresqlforeign-keys

PostgreSQL add 'on delete cascade' to all foreign keys

I am looking for a way to add on delete cascade to all foreign keys in my PostgreSQL database (preferably limitable to a schema). I have found a script here, but it doesn't seem to work for PostgreSQL. The solution doesn't have to use a script, a GUI tool would also be fine.

Thank you.

Solution

  • The recommended and supported way to do this is to add new constraints with the same definition and ON DELETE CASCADE, then drop the original constraints.

    If you are ready to risk breaking your database with a catalog modification, and you trust a random SQL statement from the internet, you could try

    WITH tables(oid) AS (
   UPDATE pg_constraint
   SET confdeltype = 'c'
   WHERE contype = 'f'
     AND confdeltype <> 'c'
     AND connamespace = 'myschema'::regnamespace
   RETURNING confrelid
)
UPDATE pg_trigger
SET tgfoid = '"RI_FKey_cascade_del"()'::regprocedure
FROM tables
WHERE tables.oid = pg_trigger.tgrelid
  AND tgtype = 9;

    Test well before using!