The list of all the queries

Perhaps a too generic foreign key column name

Query 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 about the query: The query considers both column names in English and Estonian. The query excludes foreign key columns that are covered by the primary key or a unique constraint.
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
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: Click on query to copy it

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')) 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 where the query belongs to

Collection nameCollection description
Find problems about namesA 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 automaticallyQueries, 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 where the query belongs to

Category nameCategory description
NamingQueries of this category provide information about the style of naming.
Relationships between tablesQueries 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.

Reference materials for further reading

Reference
The corresponding code smell in case of cleaning code is "N4: Unambiguous Names". (Robert C. Martin, Clean Code)

The list of all the queries