Query goal: | Find foreign key columns with the names like id, identifikaator, code, kood, number, etc. The names should have a prefix or a suffix that describes the nature of relationship with the referenced table or at least referes to the referenced table. These are too generic 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 |
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 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 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='f' 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. |
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. |