Query goal: | Find columns of base tables that name refers to the possibility that it contains unique values but the column does not belong to any PRIMARY KEY/UNIQUE constraint. If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint. |
Notes about the query: | The query finds columns that (case insensitive) name is id, code, or kood (i.e., the column possibly contains unique values) but do not belong to any PRIMARY KEY/UNIQUE constraint. The query considers both column names in English and Estonian. |
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: | Declare the PRIMARY KEY or UNIQUE constraint. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH key_columns AS (select target_schema as table_schema, target_table as table_name, a.attname as column_name from (select (select nspname from pg_namespace where oid=c.relnamespace) as target_schema, c.relname as target_table, c.oid as target_table_oid, unnest(o.conkey) AS target_col from pg_constraint o inner join pg_class c on c.oid = o.conrelid where o.contype in ('p','u')) t inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false) SELECT table_schema, table_name, column_name FROM INFORMATION_SCHEMA.columns c WHERE (table_schema, table_name) IN (SELECT table_schema, table_name FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND (column_name~'^(id|code|nr|kood|key)$') EXCEPT SELECT table_schema, table_name, column_name FROM key_columns ORDER BY table_schema, table_name; |
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 |
---|---|
Naming | Queries of this category provide information about the style of naming. |
Uniqueness | Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes. |
Validity and completeness | Queries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness). |