Query goal: | Find foreign keys with SET DEFAULT compensatory action where the foreign key column does not have a default value. Compensatory actions cannot make changes that violate integrity constraints in a database. SET DEFAULT means that there shoud be a default value at the foreign key column. |
Notes about the query: | Find foreign keys with SET DEFAULT compensating action that do not have a default value at the foreign key column. The default default is null. The query consider only simple foreign keys, i.e., foreign keys that involve only one column. |
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 action or declare a default value to the foreign key column. In order to change the compensatory action one has to drop and recreate the foreign key constraint. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH f AS (select (select nspname from pg_namespace where oid=f.relnamespace) as foreign_ns, f.relname as foreign_table, (select a.attname from pg_attribute a where a.attrelid = f.oid and a.attnum = o.confkey[1] and a.attisdropped = false) as foreign_colname, (select nspname from pg_namespace where oid=c.relnamespace) as target_ns, c.relname as target_table, (select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false) as target_colname, 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') SELECT foreign_ns, foreign_table, foreign_colname, target_ns, target_table, target_colname, CASE WHEN confupdtype='c' THEN 'CASCADE' WHEN confupdtype='r' THEN 'RESTRICT' WHEN confupdtype='n' THEN 'SET NULL' WHEN confupdtype='d' THEN 'SET DEFAULT' ELSE 'NO ACTION' END AS on_update_behavior, CASE WHEN confdeltype='c' THEN 'CASCADE' WHEN confdeltype='r' THEN 'RESTRICT' WHEN confdeltype='n' THEN 'SET NULL' WHEN confdeltype='d' THEN 'SET DEFAULT' ELSE 'NO ACTION' END AS on_delete_behavior FROM f WHERE (confupdtype='d' OR confdeltype='d') AND EXISTS (SELECT 1 FROM information_schema.columns AS c LEFT JOIN information_schema.column_domain_usage cdc ON c.table_schema=cdc.table_schema AND c.table_name=cdc.table_name AND c.column_name=cdc.column_name LEFT JOIN information_schema.domains d ON cdc.domain_schema=d.domain_schema AND cdc.domain_name=d.domain_name WHERE f.target_ns=c.table_schema AND f.target_table=c.table_name AND f.target_colname=c.column_name AND coalesce(c.column_default, domain_default) IS NULL) ORDER BY target_ns, target_table, target_colname; |
SQL query | Description |
---|---|
WITH f AS (select o.conname, (select nspname from pg_namespace where oid=c.relnamespace) as target_ns, c.relname as target_table, (select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false) as target_colname, 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' and cardinality(o.conkey)=1) SELECT format('ALTER TABLE %1$I.%2$I DROP CONSTRAINT %3$I;', target_ns, target_table, conname) AS statements FROM f WHERE (confupdtype='d' OR confdeltype='d') AND EXISTS (SELECT 1 FROM information_schema.columns AS c LEFT JOIN information_schema.column_domain_usage cdc ON c.table_schema=cdc.table_schema AND c.table_name=cdc.table_name AND c.column_name=cdc.column_name LEFT JOIN information_schema.domains d ON cdc.domain_schema=d.domain_schema AND cdc.domain_name=d.domain_name WHERE f.target_ns=c.table_schema AND f.target_table=c.table_name AND f.target_colname=c.column_name AND coalesce(c.column_default, domain_default) IS NULL) ORDER BY target_ns, target_table, target_colname; | Drop the foreign key constraint. |
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 |
---|---|
Compensating actions | Queries of this category provide information about compensating actions of foreign key constraints. |
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. |