Select FK constraints with ON UPDATE NO ACTION

Question

We use Postgres 9.5.

Is it possible to modify this script to show the ON UPDATE action for each foreign key reference at this table? Or get this values somehow?

 SELECT
     tc.table_schema, 
     tc.constraint_name, 
     tc.table_name, 
     kcu.column_name, 
     ccu.table_schema AS foreign_table_schema,
     ccu.table_name AS foreign_table_name,
     ccu.column_name AS foreign_column_name 
 FROM 
     information_schema.table_constraints AS tc 
     JOIN information_schema.key_column_usage AS kcu
       ON tc.constraint_name = kcu.constraint_name
       AND tc.table_schema = kcu.table_schema
     JOIN information_schema.constraint_column_usage AS ccu
       ON ccu.constraint_name = tc.constraint_name
       AND ccu.table_schema = tc.table_schema
WHERE constraint_type = 'FOREIGN KEY';

Erwin Brandstetter · Accepted Answer · 2018-12-11T02:37:37.707

For the "update action" as in:

FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON UPDATE CASCADE ON DELETE CASCADE
FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON UPDATE DELETE ON DELETE CASCADE
FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON UPDATE NO ACTION ON DELETE CASCADE

etc.

.. look in the system catalog table pg_constraint. It contains the column confupdtype which holds, according to the manual:

Foreign key update action code: a = no action, r = restrict, c = cascade, n = set null, d = set default

Queries

Find all FK constraints with ON UPDATE NO ACTION pointing to a given table (your question in the title):

SELECT conrelid::regclass        AS table_from
     , conname                   AS constraint_name
     , pg_get_constraintdef(oid) AS constraint_def
FROM   pg_constraint
WHERE  contype = 'f'
AND    confupdtype = 'n'
AND    confrelid = 'public.target_table'::regclass  -- target table here
ORDER  BY 1;

Inspect all FK constraints in the DB:

SELECT conrelid::regclass        AS table_from
     , conname                   AS constraint_name
     , pg_get_constraintdef(oid) AS constraint_def
     , CASE confupdtype
        WHEN 'a' THEN 'NO ACTION'
        WHEN 'r' THEN 'RESTRICT'
        WHEN 'c' THEN 'CASCADE'
        WHEN 'n' THEN 'SET NULL'
        WHEN 'd' THEN 'SET DEFAULT'
        ELSE 'WARNING: unexpected value!'
       END                       AS fk_update_action
FROM   pg_constraint
WHERE  contype = 'f'
ORDER  BY 1;

Retrieving all PK and FK

Select FK constraints with ON UPDATE NO ACTION

1 Answers1

Queries