Query goal: | Find unique index (not associated with a constraint) columns with the names like id, identifikaator, code, kood, number, etc. The names could have underscores as the prefix or suffix. These are too generic names. |
Notes about the query: | The query considers both column names in English and Estonian. |
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 |
Data source: | system catalog only |
SQL query: | Click on query to copy it
SELECT n.nspname AS table_schema, c2.relname AS table_name, a.attname AS column_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 INNER JOIN pg_catalog.pg_attribute AS a ON a.attrelid = c.oid WHERE c.relkind = 'i' AND i.indisunique=TRUE AND a.attnum>=1 AND a.attisdropped = FALSE 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 a.attname ~*'^[^[:alpha:]]*(id|kood|identifikaator|identifier|code|number)[^[:alpha:]]*$' ORDER BY table_schema, table_name, column_name; |
Collection name | Collection description |
---|---|
Find problems about names | A selection of queries that return information about the names of database objects. Contains all the types of queries - problem detection, software measure, and general overview. |
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 . |
Category name | Category 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. |
Reference |
---|
The corresponding code problem in case of cleaning code is "Add Meaningful Context". (Robert C. Martin, Clean Code) |
This is a part of an antipatterns from the Bill Karwin's book of SQL antipatterns. See Chapter 4: ID Required. |
The corresponding code smells in case of cleaning code are "N1: Choose Descriptive Names" and "N4: Unambiguous Names". (Robert C. Martin, Clean Code) |