Query goal: | Find candidate key columns with the names like id, identifikaator, code, kood, number, etc. The names should have a prefix or a 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: | High (Few or no false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Add meaningful context to the name. Rename the columns. For instance, id => person_id and code => country_code. Follow a naming convention. Use the snake case in names. |
Data source: | system catalog only |
SQL query: | Click on query to copy it
select target_schema as table_schema, target_table as table_name, a.attname as suspected_column_name, constraint_type from (select (select nspname from pg_namespace where oid=m.relnamespace) as target_schema, m.relname as target_table, m.oid as target_table_oid, unnest(o.conkey) AS target_col, CASE WHEN o.contype='p' THEN 'PRIMARY KEY' WHEN o.contype='u' THEN 'UNIQUE' END AS constraint_type from pg_constraint o inner join pg_class c on c.oid = o.conrelid inner join pg_class m on m.oid = o.conrelid where o.contype in ('u','p') and o.conrelid in (select oid from pg_class c where c.relkind = 'r')) t inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false where a.attname~*'^[_]*(id|kood|identifikaator|identifier|code|number)[_]*$' order by target_schema, target_table, attname; |
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) |