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
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.
Type
Problem detection (Each row in the result could represent a flaw in the design)
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;
Collections
This query belongs to the following collections:
Name
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 .
Categories
This query is classified under the following categories:
Name
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).