Find constraints that are perhaps badly named. Find names of constraints (directly connected to a base table) and non-unique indexes that do not contain the associated column name.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Rename the constraint or index and try to use the column name as a part of its name.
Data Source
system catalog only
SQL Query
WITH index_names AS (SELECT n.nspname AS table_schema, c2.relname AS Table_Name,
a.attname AS Column_name,
c.relname AS Index_name, 'INDEX' AS type
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c
ON a.attrelid = c.oid
LEFT JOIN pg_catalog.pg_type t
ON a.atttypid = t.oid
LEFT JOIN pg_catalog.pg_index i
ON i.indexrelid = a.attrelid
LEFT JOIN pg_catalog.pg_class c2
ON i.indrelid = c2.oid
LEFT JOIN pg_catalog.pg_authid u
ON u.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
WHERE c.relkind = 'i'
AND n.nspname <>'pg_toast' AND (n.nspname = 'public'
OR u.rolname <> 'postgres') AND NOT EXISTS (SELECT 1
FROM pg_catalog.pg_indexes AS pi
WHERE pi.schemaname=n.nspname AND
pi.tablename=c2.relname AND
pi.indexname=C.relname AND pi.indexdef LIKE '%UNIQUE%')),
unnested_constr_attributes AS (SELECT c.oid, conname, contype, conrelid, unnest(conkey) AS attnum
FROM pg_constraint c INNER JOIN pg_catalog.pg_namespace n
ON n.oid = c.connamespace
INNER JOIN pg_catalog.pg_authid u ON u.oid = n.nspowner
WHERE (n.nspname = 'public'
OR u.rolname <> 'postgres')),
constraint_names AS (
select
case when o.contype='p' then 'PRIMARY KEY'
when o.contype='u' then 'UNIQUE'
when o.contype='f' then 'FOREIGN KEY'
when o.contype='c' then 'TABLE CHECK'
when o.contype='x' then 'EXCLUDE'
when o.contype='t' then 'CONSTRAINT TRIGGER' END AS type,
(select nspname from pg_namespace where oid=c.relnamespace) as table_schema,
c.relname AS table_name,
o.conname AS constraint_name,
a.attname as column_name
from unnested_constr_attributes o inner join pg_class c on c.oid = o.conrelid
inner join pg_attribute a on a.attrelid = c.oid and a.attnum = o.attnum and a.attisdropped = false)
SELECT type, table_schema, table_name, column_name, constraint_name
FROM (SELECT type, table_schema, table_name, column_name, constraint_name
FROM constraint_names
UNION ALL SELECT type, table_schema, table_name, column_name, index_name
FROM index_names) AS foo
WHERE constraint_name NOT ILIKE '%' || column_name || '%'
ORDER BY table_schema, table_name, type, column_name;
Categories
This query is classified under the following categories:
Name
Description
Comfortability of database evolution
Queries of this category provide information about the means that influence database evolution.
Naming
Queries of this category provide information about the style of naming.