Find primary/key unique constraints (sets of columns) that are proper subsets of other primary key/unique constraints of the same table. Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys, i.e., the keys should not have redundancy in terms of columns.
Notes
PostgreSQL 14 added primary keys, unique constraints, and foreign keys to system catalogs. Thus the query was modified to exclude the results from the system catalog.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Drop the key constraints that do not enforce a candidate key.
Data Source
system catalog only
SQL Query
with keys as (select
o.conname,
nc.nspname as key_schema,
c.relname as key_table,
c.oid as key_table_oid,
o.conkey AS key_col,
case when o.contype='p' then 'PRIMARY KEY' ELSE 'UNIQUE' END as contype
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace as nc on nc.oid=c.relnamespace
inner join pg_authid AS a on nc.nspowner=a.oid
where (nc.nspname='public' or rolname<>'postgres')
and o.contype in ('u', 'p')),
keys_unnest as (select conname, key_schema, key_table, key_table_oid, key_col, key_col_num, ordin, contype
from keys, unnest(keys.key_col) with ordinality as k(key_col_num, ordin)),
keys_with_names as (select conname, key_schema, key_table, contype, array_agg(a_key.attname order by ordin) as key_col
from keys_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false
group by conname, key_schema, key_table, contype)
SELECT a.key_schema as schema, a.key_table AS table, a.contype AS superkey_type, a.conname as superkey_constraint_name, a.key_col AS superkey_col,
b.contype AS candidate_key_type, b.conname AS candidate_key_constraint_name, b.key_col AS candidate_key_col
FROM keys_with_names as a, keys_with_names as b
WHERE a.key_schema=b.key_schema AND a.key_table=b.key_table AND
NOT (a.key_col@>b.key_col AND b.key_col@>a.key_col)
AND a.key_col@>b.key_col
ORDER BY a.key_schema, a.conname, a.contype;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
with keys as (select
o.conname,
(select nspname from pg_namespace where oid=c.relnamespace) as key_schema,
c.relname as key_table,
c.oid as key_table_oid,
o.conkey AS key_col,
case when o.contype='p' then 'PRIMARY KEY' ELSE 'UNIQUE' END as contype
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('u', 'p')),
keys_unnest as (select conname, key_schema, key_table, key_table_oid, key_col, key_col_num, ordin, contype
from keys, unnest(keys.key_col) with ordinality as k(key_col_num, ordin)),
keys_with_names as (select conname, key_schema, key_table, contype, array_agg(a_key.attname order by ordin) as key_col
from keys_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false
group by conname, key_schema, key_table, contype)
SELECT format('ALTER TABLE %1$I.%2$I DROP CONSTRAINT %3$I;', a.key_schema, a.key_table, a.conname) AS statements
FROM keys_with_names as a, keys_with_names as b
WHERE a.key_schema=b.key_schema AND a.key_table=b.key_table AND
NOT (a.key_col@>b.key_col AND b.key_col@>a.key_col)
AND a.key_col@>b.key_col
ORDER BY a.key_schema, a.conname, a.contype;
Drop the constraint that enforces a superkey not a candidate key.
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
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).