If the name of an object has the suffix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find suffixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use _chk as the suffix of an index name or _pk as the suffix of a check constraint name.
Notes
The query assumes that _ is used as the separator of the components of names.
Type
Problem detection (Each row in the result could represent a flaw in the design)
WITH names AS (
SELECT n.nspname AS schema,
conname AS suspected_name,
c.relname AS container,
'TABLE' AS container_type,
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' END AS type
FROM pg_constraint o INNER JOIN pg_class c ON o.conrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND o.contype IN ('p' ,'u','f','c','x')
AND conname~'_[[:alpha:]]{1,6}[0-9]*$'
UNION SELECT
domain_schema,
constraint_name,
domain_name,
'DOMAIN' AS container_type,
'DOMAIN CHECK' AS type
FROM INFORMATION_SCHEMA.domain_constraints
WHERE domain_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 constraint_name~'_[[:alpha:]]{1,6}[0-9]*$'
UNION SELECT n.nspname, c.relname, n.nspname, 'SCHEMA' AS container_type, 'INDEX' AS type
FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace=n.oid
INNER JOIN pg_index i ON c.oid=i.indexrelid
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND c.relkind = 'i'
AND c.relname~'_[[:alpha:]]{1,6}[0-9]*$'
AND i.indisunique=false
AND i.indisexclusion=false
AND i.indisprimary=false),
suffixes AS (SELECT schema, suspected_name, container, container_type, lower(regexp_replace(suspected_name,'.*_([[:alpha:]]{1,6})[0-9]*$','\1')) AS suffix, type
FROM names)
SELECT schema, container, container_type, suspected_name, suffix AS suspected_suffix, type
FROM suffixes
WHERE (type='REFERENTIAL' AND suffix IN ('pkey', 'pk','ak','uq','unique','idx','idxfk','chk','ck','check','excl')) OR
(type IN ('TABLE CHECK','DOMAIN CHECK') AND suffix IN ('pkey','pk', 'ak','uq','unique','fk', 'fkey','idx','idxfk','excl')) OR
(type='PRIMARY KEY' AND suffix IN ('ak','uq','unique','fk','fkey','idx','idxfk','chk','ck','check','excl')) OR
(type='UNIQUE' AND suffix IN ('pkey','pk','fk','fkey','idx','idxfk','chk','check','ck','excl')) OR
(type='EXCLUDE' AND suffix IN ('pkey','pk','fk','fkey','idx','idxfk','chk','check','ck')) OR
(type='INDEX' AND suffix IN ('pkey','pk','ak','uq','unique','fk','fkey','chk','check','ck','excl'))
ORDER BY type, schema, suffix;
Collections
This query belongs to the following collections:
Name
Description
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
CHECK constraints
Queries of this category provide information about CHECK constraints.
Naming
Queries of this category provide information about the style of naming.
Regular expressions
Queries of this catergory provide information about the use of regular expressions.
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
The corresponding code smells in case of cleaning code are "N1: Choose Descriptive Names" and "N6: Avoid Encodings". (Robert C. Martin, Clean Code)