Perhaps a too generic foreign key column name

Goal	Find the names of foreign key columns that are too generic. The expressive names of table columns allow database users better and more quickly understand the meaning of data in the database. A person could participate in a process or be associated with an object due to different reasons. Thus, foreign key column names like isik_id, person_id, tootaja_id, worker_id etc. are too generic. The name should refer (also) to the reason why the person is connected.
Notes	The query considers both column names in English and Estonian. The query excludes foreign key columns that are covered by the primary key, unique, or exclude constraint.
Type	Problem detection Each row in the result could represent a flaw in the design
Reliability	Medium Medium number of false-positive results
License	MIT (opens in new tab)
Fixing Suggestion	Rename the columns and give more specific names. For instance, instead of having column worker_id in the table Contract use the column name registrator_id or worker_registrator_id.
Data Source	system catalog only
SQL Query	with fk as (select target_schema as table_schema, target_table as table_name, a.attname as column_name from (select (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 where o.contype = 'f') t inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false), keys as (select target_schema as table_schema, target_table as table_name, a.attname as column_name from (select (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 where o.contype in ('u','p','x')) t inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false) SELECT table_schema, table_name, column_name FROM fk F WHERE column_name~*('^(isikukood\|personal_code\|((id\|kood\|code)(_){0,1}){0,1}(isik\|tootaja\|kasutaja\|subjekt\|person\|worker\|user\|subject)((_){0,1}(id\|kood\|code)){0,1})$') AND NOT EXISTS (SELECT 1 FROM keys AS K WHERE K.table_schema=F.table_schema AND K.table_name=F.table_name AND K.column_name=F.column_name) ORDER BY table_schema, table_name, column_name;

Collections

This query belongs to the following collections:

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 .

Name	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 .

Categories

This query is classified under the following categories:

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.

Name	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.