Find unique indexes that have too generic names like "key" or the name contain too generic words like "data" (all constraints restrict data in the table), or the name is an abbreviation of a constraint type name.
Notes
The query considers both names in English and in Estonian.
Type
Problem detection (Each row in the result could represent a flaw in the design)
SELECT
n.nspname AS table_schema,
c2.relname AS table_name,
c.relname AS index_name
FROM
pg_catalog.pg_class AS c INNER JOIN pg_catalog.pg_index AS i ON i.indexrelid = c.oid
INNER JOIN pg_catalog.pg_class AS c2 ON i.indrelid = c2.oid
INNER JOIN pg_catalog.pg_namespace AS n ON c.relnamespace = n.oid
INNER JOIN pg_catalog.pg_authid AS u ON n.nspowner = u.oid
WHERE c.relkind = 'i'
AND i.indisunique=TRUE
AND (n.nspname = 'public'
OR u.rolname <> 'postgres')
AND NOT EXISTS (SELECT * FROM pg_catalog.pg_constraint AS o WHERE o.conindid=i.indexrelid AND o.contype IN ('u','p'))
AND (c.relname ~*'(andme(?!baas)|data(?!base)|info|veerg|column|kitsendus|piirang|constraint|restriction)'
OR (c.relname~*'^[^[:alpha:]]*(pk|uk|uq|ak|ck|chk|fk|excl|key|relationship|id|voti|seos|suhe)[^[:alpha:]]*$'))
ORDER BY table_schema, table_name, index_name;
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
Naming
Queries of this category provide information about the style of naming.
Uniqueness
Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.