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 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 |
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') ) 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; |
Collection name | Collection description |
---|---|
Find problems automatically | Queries, 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 . |
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. |