Query goal: | Naming of foreign key and referenced candidate key columns should be consistent. It cannot be so that in one table a value is labeled "id" like some surrogate key value and in another it "turns" into human-usable "code" or vice versa. An example: Person(person_id, name) Primary Key (person_id) E_mail_address(e_mail_address_id, person_code, address) Primary Key (e_mail_address_id) Foreign key (person_code) References Person (person_id) |
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: | Rename the columns. In case of the example: Person(person_id, name) Primary Key (person_id) E_mail_address(e_mail_address_id, person_id, address) Primary Key (e_mail_address_id) Foreign Key (person_id) References Person (person_id) |
Data source: | system catalog only |
SQL query: | Click on query to copy it
with fk as (select o.conname, (select nspname from pg_namespace where oid=f.relnamespace) as foreign_schema, f.relname as foreign_table, f.oid as foreign_table_oid, unnest(o.confkey) AS foreign_col, (select nspname from pg_namespace where oid=c.relnamespace) as target_schema, c.relname as target_table, c.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 f on f.oid = o.confrelid where o.contype = 'f'), fk_columns as ( select fk.conname, fk.foreign_schema, fk.foreign_table, a_foreign.attname as foreign_col, fk.target_schema, fk.target_table, a_target.attname as target_col from fk inner join pg_attribute a_foreign on fk.foreign_col = a_foreign.attnum and fk.foreign_table_oid = a_foreign.attrelid and a_foreign.attisdropped = false inner join pg_attribute a_target on fk.target_col = a_target.attnum and fk.target_table_oid = a_target.attrelid and a_target.attisdropped = false) select * from fk_columns where ((foreign_col~*'id$' or foreign_col~*'^[_]{0,1}id_') and (target_col~*'_(kood|code|nr)$' or target_col~*'^[_]{0,1}(kood|code|nr)_')) or ((foreign_col~*'_(kood|code|nr)$' or foreign_col~*'^[_]{0,1}(kood|code|nr)_') and (target_col~*'id$' or target_col~*'^[_]{0,1}id_')) order by target_schema, target_table, conname; |
Collection name | Collection 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 . |
Category name | Category description |
---|---|
Inconsistencies | Queries of this catergory provide information about inconsistencies of solving the same problem in different places. |
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. |