Inconsistent chain of relationships in terms of using ON UPDATE compensating action

Back to Catalog
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 compensating action should be consistent. 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
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability High (Few or no false-positive results)
License MIT License
Fixing Suggestion Change the compensatory actions of the involved foreign key constraints.
Data Source system catalog only
SQL Query 
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')
)
or (
(fk.confupdtype='n' and fk2.confupdtype<>'n') 
or (fk.confupdtype<>'n' and fk2.confupdtype='n')
) or (
(fk.confupdtype='d' and fk2.confupdtype<>'d') 
or (fk.confupdtype<>'d' and fk2.confupdtype='d')
) or (
(fk.confupdtype='r' and fk2.confupdtype<>'r') 
or (fk.confupdtype<>'r' and fk2.confupdtype='r')
) or (
(fk.confupdtype='a' and fk2.confupdtype<>'a') 
or (fk.confupdtype<>'a' and fk2.confupdtype='a')
)
)
order by fk.target_schema, fk.target_table;
Collections

This query belongs to the following collections:

NameDescription
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories

This query is classified under the following categories:

NameDescription
Comfortability of data managementQueries 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 actionsQueries of this category provide information about compensating actions of foreign key constraints.
InconsistenciesQueries of this catergory provide information about inconsistencies of solving the same problem in different places.
Relationships between tablesQueries 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.