Query goal: | Find foreign key constraints that referenced column is optional. |
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: | The referenced column should be mandatory, i.e., NULLs are not permitted. Declare to the referenced column NOT NULL constraint. |
Data source: | INFORMATION_SCHEMA+system catalog |
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, CASE WHEN o.confupdtype='a' THEN 'NO ACTION' WHEN o.confupdtype='r' THEN 'RESTRICT' WHEN o.confupdtype='c' THEN 'CASCADE' WHEN o.confupdtype='n' THEN 'SET NULL' WHEN o.confupdtype='d' THEN 'SET DEFAULT' END AS on_update, CASE WHEN o.confdeltype='a' THEN 'NO ACTION' WHEN o.confdeltype='r' THEN 'RESTRICT' WHEN o.confdeltype='c' THEN 'CASCADE' WHEN o.confdeltype='n' THEN 'SET NULL' WHEN o.confdeltype='d' THEN 'SET DEFAULT' END AS on_delete 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, on_update, on_delete 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, array_agg(a_target.attname order by ordin) as target_col, on_update, on_delete 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, a_foreign.attname, target_schema, target_table,on_update, on_delete), optional_cols AS (SELECT C.table_schema, C.table_name, C.column_name FROM information_schema.columns C INNER JOIN information_schema.tables T USING (table_schema, table_name) INNER JOIN information_schema.schemata S ON T.table_schema = S.schema_name WHERE T.table_type = 'BASE TABLE' AND C.is_nullable='YES' AND (T.table_schema = 'public' OR S.schema_owner<>'postgres')) SELECT conname, foreign_schema, foreign_table, foreign_col, target_schema, target_table, target_col, on_update, on_delete FROM fk_with_names AS f INNER JOIN optional_cols AS o ON f.foreign_schema=o.table_schema AND f.foreign_table=o.table_name AND f.foreign_col=o.column_name ORDER BY target_schema, target_table, conname; |
SQL query | Description |
---|---|
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, CASE WHEN o.confupdtype='a' THEN 'NO ACTION' WHEN o.confupdtype='r' THEN 'RESTRICT' WHEN o.confupdtype='c' THEN 'CASCADE' WHEN o.confupdtype='n' THEN 'SET NULL' WHEN o.confupdtype='d' THEN 'SET DEFAULT' END AS on_update, CASE WHEN o.confdeltype='a' THEN 'NO ACTION' WHEN o.confdeltype='r' THEN 'RESTRICT' WHEN o.confdeltype='c' THEN 'CASCADE' WHEN o.confdeltype='n' THEN 'SET NULL' WHEN o.confdeltype='d' THEN 'SET DEFAULT' END AS on_delete 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, on_update, on_delete 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, array_agg(a_target.attname order by ordin) as target_col, on_update, on_delete 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, a_foreign.attname, target_schema, target_table,on_update, on_delete), optional_cols AS (SELECT C.table_schema, C.table_name, C.column_name FROM information_schema.columns C INNER JOIN information_schema.tables T USING (table_schema, table_name) INNER JOIN information_schema.schemata S ON T.table_schema = S.schema_name WHERE T.table_type = 'BASE TABLE' AND C.is_nullable='YES' AND (T.table_schema = 'public' OR S.schema_owner<>'postgres')) SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I SET NOT NULL;', foreign_schema, foreign_table, foreign_col) AS statements FROM fk_with_names AS f INNER JOIN optional_cols AS o ON f.foreign_schema=o.table_schema AND f.foreign_table=o.table_name AND f.foreign_col=o.column_name ORDER BY target_schema, target_table, conname; | Declare NOT NULL constraint to the column. |
Collection name | Collection description |
---|---|
Find problems about integrity constraints | A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview |
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 |
---|---|
Missing data | Queries of this category provide information about missing data (NULLs) in a database. |
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. |
Uniqueness | Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes. |
Reference |
---|
Balogh, G., Gergely, T., Beszédes, Á., Szarka, A., Fábián, Z.: Capturing expert knowledge to guide data flow and structure analysis of large corporate databases. Acta Polytechnica Hungarica 16(4), 7–26 (2019). (A foreign key does not refer to a key) |