Inconsistent chain of relationships in terms of using ON UPDATE CASCADE

with fk as (select 
o.conname,
(select nspname from pg_namespace where oid=f.relnamespace) as foreign_schema,
f.relname as foreign_table,
f.oid as foreign_table_oid,
o.confkey AS foreign_col,
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table, 
c.oid as target_table_oid,
o.conkey AS target_col,
o.confupdtype,
o.confdeltype
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_class f on f.oid = o.confrelid 
where o.contype = 'f'),
fk_unnest as (select conname, foreign_schema, foreign_table,  foreign_table_oid, foreign_col, foreign_col_num, target_schema, target_table, target_table_oid, target_col, target_col_num, ordin, confupdtype, confdeltype
from fk, unnest(fk.foreign_col, fk. target_col) with ordinality as f(foreign_col_num, target_col_num, ordin)),
fk_with_names as (select conname, foreign_schema, foreign_table, array_agg(a_foreign.attname order by ordin) as foreign_col, target_schema, target_table, array_agg(a_target.attname order by ordin) as target_col, confupdtype, confdeltype
from fk_unnest fk inner join pg_attribute a_foreign on fk.foreign_col_num = a_foreign.attnum and fk.foreign_table_oid = a_foreign.attrelid and a_foreign.attisdropped = false
inner join pg_attribute a_target on fk.target_col_num = a_target.attnum and fk.target_table_oid = a_target.attrelid and a_target.attisdropped = false
group by conname, foreign_schema, foreign_table, target_schema, target_table, confupdtype,confdeltype)
select fk.foreign_schema foreign_schema1, fk.foreign_table foreign_table1, fk.foreign_col foreign_col1, fk.conname conname1, 
fk.target_schema target_schema1, fk.target_table target_table1, fk.target_col target_col1,  
case when fk.confupdtype='a' then 'ON UPDATE NO ACTION' when fk.confupdtype='r' then 'ON UPDATE RESTRICT' when fk.confupdtype='c' then 'ON UPDATE CASCADE' when fk.confupdtype='n' then 'ON UPDATE SET NULL' when fk.confupdtype='d' then 'ON UPDATE SET DEFAULT' end as compensatory_action1,
fk2.target_schema target_schema2, fk2.target_table target_table2, fk2.target_col target_col2,  
case when fk2.confupdtype='a' then 'ON UPDATE NO ACTION' when fk2.confupdtype='r' then 'ON UPDATE RESTRICT' when fk2.confupdtype='c' then 'ON UPDATE CASCADE' when fk2.confupdtype='n' then 'ON UPDATE SET NULL' when fk2.confupdtype='d' then 'ON UPDATE SET DEFAULT' end as compensatory_action2
from fk_with_names fk, fk_with_names fk2
where fk.target_schema=fk2.foreign_schema and fk.target_table=fk2.foreign_table and fk.target_col=fk2.foreign_col and ((fk.confupdtype='c' and fk2.confupdtype<>'c') or (fk.confupdtype<>'c' and fk2.confupdtype='c'))
order by fk.target_schema, fk.target_table;

Query goal:	In case of a chain of relationships between tables (where the primary key and the foreign key have the same columns) the use of ON UPDATE CASCADE should be consistent - either all the involved foreign keys have ON UPDATE CASCADE compensating action or none of these have it. For instance, in the next example there is inconsistency, because if one changes the person_code in table Person, then the modification does not succeed because it does not cascade to the table Product. It is unclear as to whether it should be possible to change the person_code or not. Person (person_code, surname) Primary key (person_code) Worker(person_code) Primary key (person_code) Foreign key (person_code) References Person (person_code) ON UPDATE CASCADE Product(product_code, registrator) Primary key (product_code) Foreign key (registrator) References Worker (person_code) ON UPDATE NO ACTION
Query type:	Problem detection (Each row in the result could represent a flaw in the design)
Query reliability:	High (Few or no false-positive results)
Query license:	MIT License
Fixing suggestion:	Change the compensatory actions of the involved foreign key constraints.
Data source:	system catalog only
SQL query:	Click on query to copy it with fk as (select o.conname, (select nspname from pg_namespace where oid=f.relnamespace) as foreign_schema, f.relname as foreign_table, f.oid as foreign_table_oid, o.confkey AS foreign_col, (select nspname from pg_namespace where oid=c.relnamespace) as target_schema, c.relname as target_table, c.oid as target_table_oid, o.conkey AS target_col, o.confupdtype, o.confdeltype from pg_constraint o inner join pg_class c on c.oid = o.conrelid inner join pg_class f on f.oid = o.confrelid where o.contype = 'f'), fk_unnest as (select conname, foreign_schema, foreign_table, foreign_table_oid, foreign_col, foreign_col_num, target_schema, target_table, target_table_oid, target_col, target_col_num, ordin, confupdtype, confdeltype from fk, unnest(fk.foreign_col, fk. target_col) with ordinality as f(foreign_col_num, target_col_num, ordin)), fk_with_names as (select conname, foreign_schema, foreign_table, array_agg(a_foreign.attname order by ordin) as foreign_col, target_schema, target_table, array_agg(a_target.attname order by ordin) as target_col, confupdtype, confdeltype from fk_unnest fk inner join pg_attribute a_foreign on fk.foreign_col_num = a_foreign.attnum and fk.foreign_table_oid = a_foreign.attrelid and a_foreign.attisdropped = false inner join pg_attribute a_target on fk.target_col_num = a_target.attnum and fk.target_table_oid = a_target.attrelid and a_target.attisdropped = false group by conname, foreign_schema, foreign_table, target_schema, target_table, confupdtype,confdeltype) select fk.foreign_schema foreign_schema1, fk.foreign_table foreign_table1, fk.foreign_col foreign_col1, fk.conname conname1, fk.target_schema target_schema1, fk.target_table target_table1, fk.target_col target_col1, case when fk.confupdtype='a' then 'ON UPDATE NO ACTION' when fk.confupdtype='r' then 'ON UPDATE RESTRICT' when fk.confupdtype='c' then 'ON UPDATE CASCADE' when fk.confupdtype='n' then 'ON UPDATE SET NULL' when fk.confupdtype='d' then 'ON UPDATE SET DEFAULT' end as compensatory_action1, fk2.target_schema target_schema2, fk2.target_table target_table2, fk2.target_col target_col2, case when fk2.confupdtype='a' then 'ON UPDATE NO ACTION' when fk2.confupdtype='r' then 'ON UPDATE RESTRICT' when fk2.confupdtype='c' then 'ON UPDATE CASCADE' when fk2.confupdtype='n' then 'ON UPDATE SET NULL' when fk2.confupdtype='d' then 'ON UPDATE SET DEFAULT' end as compensatory_action2 from fk_with_names fk, fk_with_names fk2 where fk.target_schema=fk2.foreign_schema and fk.target_table=fk2.foreign_table and fk.target_col=fk2.foreign_col and ((fk.confupdtype='c' and fk2.confupdtype<>'c') or (fk.confupdtype<>'c' and fk2.confupdtype='c')) order by fk.target_schema, fk.target_table;

Category name	Category description
Comfortability of data management	Queries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient.
Compensating actions	Queries of this category provide information about compensating actions of foreign key constraints.
Inconsistencies	Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
Relationships between tables	Queries of this category provide information about how database tables are connected to each other and whether such connections have been explicitly defined and whether it has been done correctly.

Inconsistent chain of relationships in terms of using ON UPDATE CASCADE

Collections where the query belongs to

Categories where the query belongs to