Query goal: | Find columns of base tables that contain automatically generated unique values but do 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 are associated with an external or internal sequence generator (i.e., contain unique values) but do not belong to any PRIMARY KEY/UNIQUE constraint. The query takes into account a possibility that a column may be associated with a sequence generator through a domain. |
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), seq_columns AS (SELECT table_schema, table_name, column_name FROM INFORMATION_SCHEMA.columns c LEFT JOIN information_schema.domains d USING (domain_schema, domain_name) 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 (coalesce (c.column_default, d.domain_default) ILIKE '%nextval%' OR is_identity='YES')) SELECT table_schema, table_name, column_name FROM seq_columns 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 |
---|---|
Default value | Queries of this catergory provide information about the use of default values. |
Sequence generators | Queries of this category provide information about sequence generators and their usage. |
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). |