Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with two or three columns
Find patterns of the names of constraints and indexes. Make sure that the naming is consistent.
Notes
The query replaces in the constraint/index name table and column names with keywords column, table, foreign table. These words are between (b) tags to improve readability of the result in the web. In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser. If the number of pattern occurrences is smaller than five, then the query presents constraint name as well as column names and table names with which the constraint is associated. 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
General (Overview of some aspect of the database.)
WITH key_constraint_names AS (select
CASE WHEN contype='p' THEN 'PRIMARY KEY' ELSE 'UNIQUE' END AS type,
o.conname AS constraint_name,
c.relname as table_name,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false),'') as column_name1,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[2] and a.attisdropped = false),'') as column_name2,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[3] and a.attisdropped = false),'') as column_name3
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') and cardinality(o.conkey) IN (2,3)),
chk_constraint_names AS (select
'CHECK' AS type,
o.conname AS constraint_name,
c.relname as table_name,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false),'') as column_name1,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[2] and a.attisdropped = false),'') as column_name2,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[3] and a.attisdropped = false),'') as column_name3
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 = 'c' and cardinality(o.conkey) IN (2,3)),
exclude_constraint_names AS (select
'EXCLUDE' AS type,
o.conname AS constraint_name,
c.relname as table_name,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false),'') as column_name1,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[2] and a.attisdropped = false),'') as column_name2,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[3] and a.attisdropped = false),'') as column_name3
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 = 'x' and cardinality(o.conkey) IN (2,3)),
fk_constraint_names AS (select
'FOREIGN KEY' AS type,
o.conname,
(select nspname from pg_namespace where oid=f.relnamespace) as foreign_ns,
f.relname as foreign_table,
coalesce((select a.attname from pg_attribute a where a.attrelid = f.oid and a.attnum = o.confkey[1] and a.attisdropped = false),'') as foreign_colname1,
coalesce((select a.attname from pg_attribute a where a.attrelid = f.oid and a.attnum = o.confkey[2] and a.attisdropped = false),'') as foreign_colname2,
coalesce((select a.attname from pg_attribute a where a.attrelid = f.oid and a.attnum = o.confkey[3] and a.attisdropped = false),'') as foreign_colname3,
(select nspname from pg_namespace where oid=c.relnamespace) as target_ns,
c.relname as target_table,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false),'') as target_colname1,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[2] and a.attisdropped = false),'') as target_colname2,
coalesce((select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[3] and a.attisdropped = false),'') as target_colname3
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_class f on f.oid = o.confrelid
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 = 'f' and cardinality(o.conkey) IN (2,3)),
index_with_col_name AS (select
'INDEX' AS type,
ci.relname as index_name,
n.nspname as schema_name,
i.indrelid as table_oid,
ct.relname as table_name,
coalesce((select a.attname from pg_attribute a where a.attrelid = ct.oid and a.attnum = i.indkey[0] and a.attisdropped = false),'') as column_name1,
coalesce((select a.attname from pg_attribute a where a.attrelid = ct.oid and a.attnum = i.indkey[1] and a.attisdropped = false),'') as column_name2,
coalesce((select a.attname from pg_attribute a where a.attrelid = ct.oid and a.attnum = i.indkey[2] and a.attisdropped = false),'') as column_name3
from pg_catalog.pg_index i inner join pg_catalog.pg_class ci on i.indexrelid=ci.oid
inner join pg_catalog.pg_class ct on i.indrelid=ct.oid
inner join pg_catalog.pg_namespace n on ct.relnamespace=n.oid
where indnatts IN (2,3) and indisunique=false and
n.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT pattern, string_agg(DISTINCT type,';' ORDER BY type DESC) AS types, Count(*) AS number_of_occurrences, CASE WHEN Count(*)<5 THEN string_agg(constraint_name || ' (' || pattern_source || ')','; ' ORDER BY constraint_name ASC) ELSE NULL END AS names
FROM (
SELECT
CASE WHEN (lower(column_name1) IN ('id','code') OR lower(column_name1)=lower(table_name))
OR (lower(column_name2) IN ('id','code') OR lower(column_name2)=lower(table_name))
OR (lower(column_name3) IN ('id','code') OR lower(column_name3)=lower(table_name))
THEN
replace(replace(replace(replace(lower(constraint_name), lower(table_name),'table'), lower(column_name1), 'column1'), lower(column_name2), 'column2'), lower(column_name3), 'column3')
ELSE
replace(replace(replace(replace(lower(constraint_name), lower(column_name1),'column1'), lower(column_name2),'column2'), lower(column_name3),'column3'), lower(table_name), 'table') END AS pattern, type, constraint_name, table_name || ':' || column_name1 || ', ' || column_name2 || CASE WHEN column_name3<>'' THEN ', ' || column_name3 ELSE '' END AS pattern_source
FROM key_constraint_names
UNION ALL SELECT replace(replace(replace(replace(lower(constraint_name), lower(column_name1),'column1'), lower(column_name2),'column2'), lower(column_name3),'column3'), lower(table_name), 'table') AS pattern, type, constraint_name,
case when column_name1='' then table_name else table_name || ':' || column_name1 || ', ' || column_name2 || CASE WHEN column_name3<>'' THEN ', ' || column_name3 ELSE '' END end AS pattern_source
FROM exclude_constraint_names
UNION ALL SELECT replace(replace(replace(replace(lower(index_name), lower(column_name1),'column1'), lower(column_name2),'column2'), lower(column_name3),'column3'), lower(table_name), 'table') AS pattern, type, index_name, table_name || ':' || column_name1 || ', ' || column_name2 || CASE WHEN column_name3<>'' THEN ', ' || column_name3 ELSE '' END AS pattern_source
FROM index_with_col_name
UNION ALL SELECT replace(replace(replace(replace(lower(constraint_name), lower(column_name1),'column1'), lower(column_name2),'column2'), lower(column_name3),'column3'), lower(table_name), 'table') AS pattern, type, constraint_name, table_name || ':' || column_name1 || ', ' || column_name2 || CASE WHEN column_name3<>'' THEN ', ' || column_name3 ELSE '' END AS pattern_source
FROM chk_constraint_names
UNION ALL SELECT
CASE
WHEN (foreign_table ILIKE '%' || target_table || '%' AND foreign_table<>target_table) THEN
replace(replace(replace(replace(replace(lower(conname), lower(target_colname1),'column1'), lower(target_colname2),'column2'), lower(target_colname3),'column3'), lower(foreign_table), 'foreign_table'), lower(target_table), 'table')
ELSE
replace(replace(replace(replace(replace(lower(conname), lower(target_colname1),'column1'), lower(target_colname2),'column2'), lower(target_colname3),'column3'), lower(target_table), 'table'), lower(foreign_table), 'foreign_table')
END AS pattern, type, conname,
'primary: ' || foreign_table || ':' || foreign_colname1 || ', ' || foreign_colname2 || CASE WHEN foreign_colname3<>'' THEN ', ' || foreign_colname3 ELSE '' END ||
' child: ' || target_table || ':' || target_colname1 || ', ' || target_colname2 || CASE WHEN target_colname3<>'' THEN ', ' || target_colname3 ELSE '' END AS pattern_source
FROM fk_constraint_names
) AS foo
GROUP BY pattern
ORDER BY types, Count(*) DESC, pattern;
Collections
This query belongs to the following collections:
Name
Description
Find problems by overview
Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Lexicon bad smells and linguistic antipatterns
Queries made to find the occurrences of lexicon bad smells and linguistic antipatterns
Categories
This query is classified under the following categories:
Name
Description
CHECK constraints
Queries of this category provide information about CHECK constraints.
Comfortability of data management
Queries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient.
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
Naming
Queries of this category provide information about the style of naming.
Relationships between tables
Queries of this category provide information about how database tables are connected to each other and whether such connections have been explicitly defined and whether it has been done correctly.
Uniqueness
Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.
Further reading and related materials:
Reference
Smell "Identifier construction rules": Abebe, S.L., Haiduc, S., Tonella, P., Marcus, A., 2011. The effect of lexicon bad smells on concept location in source code. In 2011 IEEE 11th International Working Conference on Source Code Analysis and Manipulation (pp. 125-134). IEEE.