Incorrect prefix of a constraint name or an index name

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:]]|_)[[:alpha:]]{0,6}_'

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:]]|_)[[:alpha:]]{0,6}_'

UNION SELECT n.nspname, c.relname, n.nspname, 'SCHEMA' AS container_type, 'SECONDARY 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:]]|_)[[:alpha:]]{0,6}_' 
AND i.indisunique=false
AND i.indisexclusion=false

UNION SELECT n.nspname, c.relname, n.nspname, 'SCHEMA' AS container_type, 'UNIQUE 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:]]|_)[[:alpha:]]{0,6}_' 
AND (i.indisunique=true
OR i.indisexclusion=true)
AND NOT EXISTS (SELECT *
FROM pg_constraint AS pc
WHERE i.indexrelid=pc.conindid
AND pc.contype IN ('p' ,'u','x'))),

prefixes AS (SELECT schema, suspected_name, container, container_type, lower(regexp_replace(suspected_name,'^(([[:alpha:]]|_)[[:alpha:]]{0,6})_.*','\1')) AS prefix, 
translate(lower(regexp_replace(suspected_name,'^(([[:alpha:]]|_)[[:alpha:]]{0,6})_.*','\1')),'_','') AS prefix_cleaned, type
FROM names)

SELECT schema, container, container_type, suspected_name, prefix AS suspected_prefix, type
FROM prefixes
WHERE (type='REFERENTIAL' AND prefix_cleaned IN ('pkey', 'pk','ak','uq','unique','idx','idxfk','chk','ck','check','excl')) OR
(type IN ('TABLE CHECK','DOMAIN CHECK') AND prefix_cleaned IN ('pkey','pk', 'ak','uq','unique','fk', 'fkey','idx','idxfk','excl')) OR
(type='PRIMARY KEY' AND prefix_cleaned IN ('ak','uq','unique','fk','fkey','idx','idxfk','chk','ck','check','excl')) OR
(type='UNIQUE' AND prefix_cleaned IN ('pkey','pk','fk','fkey','idx','idxfk','chk','check','ck','excl')) OR
(type='EXCLUDE' AND prefix_cleaned IN ('pkey','pk', 'fk','fkey','idx','idxfk','chk','check','ck')) OR
(type='SECONDARY INDEX' AND prefix_cleaned IN ('pkey','pk','ak','uq','unique','fk','fkey','chk','check','ck')) OR
(type='UNIQUE INDEX' AND prefix_cleaned IN ('pkey','pk', 'fk','fkey','chk','check','ck'))
ORDER BY type, schema, prefix;

Query goal:	If the name of an object has the prefix 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 prefixes 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 prefix of an index name or pk_ as the prefix of a check constraint name.
Notes about the query:	The query assumes that _ is used as the separator of the components of names.
Query type:	Problem detection (Each row in the result could represent a flaw in the design)
Query reliability:	Medium (Medium number of false-positive results)
Query license:	MIT License
Fixing suggestion:	Use correct prefixes or consider using a naming scheme that does not suggest the use of prefixes.
Data source:	INFORMATION_SCHEMA+system catalog
SQL query:	Click on query to copy it 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:]]\|_)[[:alpha:]]{0,6}_' 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:]]\|_)[[:alpha:]]{0,6}_' UNION SELECT n.nspname, c.relname, n.nspname, 'SCHEMA' AS container_type, 'SECONDARY 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:]]\|_)[[:alpha:]]{0,6}_' AND i.indisunique=false AND i.indisexclusion=false UNION SELECT n.nspname, c.relname, n.nspname, 'SCHEMA' AS container_type, 'UNIQUE 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:]]\|_)[[:alpha:]]{0,6}_' AND (i.indisunique=true OR i.indisexclusion=true) AND NOT EXISTS (SELECT * FROM pg_constraint AS pc WHERE i.indexrelid=pc.conindid AND pc.contype IN ('p' ,'u','x'))), prefixes AS (SELECT schema, suspected_name, container, container_type, lower(regexp_replace(suspected_name,'^(([[:alpha:]]\|_)[[:alpha:]]{0,6})_.','\1')) AS prefix, translate(lower(regexp_replace(suspected_name,'^(([[:alpha:]]\|_)[[:alpha:]]{0,6})_.','\1')),'_','') AS prefix_cleaned, type FROM names) SELECT schema, container, container_type, suspected_name, prefix AS suspected_prefix, type FROM prefixes WHERE (type='REFERENTIAL' AND prefix_cleaned IN ('pkey', 'pk','ak','uq','unique','idx','idxfk','chk','ck','check','excl')) OR (type IN ('TABLE CHECK','DOMAIN CHECK') AND prefix_cleaned IN ('pkey','pk', 'ak','uq','unique','fk', 'fkey','idx','idxfk','excl')) OR (type='PRIMARY KEY' AND prefix_cleaned IN ('ak','uq','unique','fk','fkey','idx','idxfk','chk','ck','check','excl')) OR (type='UNIQUE' AND prefix_cleaned IN ('pkey','pk','fk','fkey','idx','idxfk','chk','check','ck','excl')) OR (type='EXCLUDE' AND prefix_cleaned IN ('pkey','pk', 'fk','fkey','idx','idxfk','chk','check','ck')) OR (type='SECONDARY INDEX' AND prefix_cleaned IN ('pkey','pk','ak','uq','unique','fk','fkey','chk','check','ck')) OR (type='UNIQUE INDEX' AND prefix_cleaned IN ('pkey','pk', 'fk','fkey','chk','check','ck')) ORDER BY type, schema, prefix;

Category name	Category description
CHECK constraints	Queries of this category provide information about CHECK constraints.
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.

Incorrect prefix of a constraint name or an index name

Collections where the query belongs to

Categories where the query belongs to

Reference materials for further reading