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
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)
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
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, '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'))),
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='SECONDARY INDEX' AND suffix IN ('pkey','pk','ak','uq','unique','fk','fkey','chk','check','ck')) OR
(type='UNIQUE INDEX' AND suffix IN ('pkey','pk', 'fk','fkey','chk','check','ck'))
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.
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
The corresponding code smells in case of cleaning code are "N1: Choose Descriptive Names" and "N6: Avoid Encodings". (Robert C. Martin, Clean Code)