Query goal: | Find foreign key columns that are covered by a simple check constraint, i.e., the constraint involves only one column. Look only constraints that are directly associated with the table, i.e., are not specified through a domain. Perhaps the constraint should be defined on the referenced candidate key column. |
Notes about the query: | In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser. The query excludes domains because it is appropriate to use the same domain in case of both foreign key and referenced candidate key column. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | Medium (Medium number of false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Drop the check constraint and create it on the referenced candidate key column if it is not already there. |
Data source: | system catalog only |
SQL query: | Click on query to copy it
WITH simple_check_constraints AS ( select n.nspname as table_schema, c.relname as table_name, a.attname as column_name, o.conname, pg_get_constraintdef(o.oid) AS constraint_def from pg_constraint o inner join pg_class c on c.oid = o.conrelid inner join pg_namespace n on o.connamespace=n.oid INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE where o.contype = 'c' AND cardinality(o.conkey)=1), 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 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 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, a_foreign.attname as foreign_col, target_schema, target_table, a_target.attname as target_col 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) SELECT scc.table_schema, scc.table_name, scc.column_name, string_agg(DISTINCT scc.conname || ' ' || scc.constraint_def, ';<br>' ORDER BY scc.conname || ' ' || scc.constraint_def) AS checks_on_fk, f.conname AS fk_constraint_name, f.foreign_schema, f.foreign_table, f.foreign_col, string_agg(DISTINCT coalesce(sccf.conname,'') || ' ' || coalesce(sccf.constraint_def,''), ';<br>' ORDER BY coalesce(sccf.conname,'') || ' ' || coalesce(sccf.constraint_def,'')) AS checks_on_candidate_key FROM simple_check_constraints AS scc INNER JOIN fk_with_names AS f ON f.target_schema=scc.table_schema AND f.target_table=scc.table_name AND f.target_col=scc.column_name LEFT JOIN simple_check_constraints AS sccf ON f.foreign_schema=sccf.table_schema AND f.foreign_table=sccf.table_name AND f.foreign_col=sccf.column_name GROUP BY scc.table_schema, scc.table_name, scc.column_name, f.conname, f.foreign_schema, f.foreign_table, f.foreign_col ORDER BY scc.table_schema, scc.table_name, scc.column_name; |
SQL query | Description |
---|---|
WITH simple_check_constraints AS ( select n.nspname as table_schema, c.relname as table_name, a.attname as column_name, o.conname, pg_get_constraintdef(o.oid) AS constraint_def from pg_constraint o inner join pg_class c on c.oid = o.conrelid inner join pg_namespace n on o.connamespace=n.oid INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE where o.contype = 'c' AND cardinality(o.conkey)=1), 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 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 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, a_foreign.attname as foreign_col, target_schema, target_table, a_target.attname as target_col 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) SELECT format('ALTER TABLE %1$I.%2$I DROP CONSTRAINT %3$I;', scc.table_schema, scc.table_name, scc.conname) AS statements FROM simple_check_constraints AS scc WHERE EXISTS (SELECT * FROM fk_with_names AS f WHERE f.target_schema=scc.table_schema AND f.target_table=scc.table_name AND f.target_col=scc.column_name) ORDER BY scc.table_schema, scc.table_name, scc.conname; | Drop the check 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 |
---|---|
CHECK constraints | Queries of this category provide information about CHECK 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. |