Find simple primary keys that column name does not contain the table name. The naming should be clear and consistent.
Notes
The query excludes primary key columns that are also foreign key columns because the name of these columns could be derived based on another (referenced) table.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Rename the column so that the column name contains the table name.
Data Source
system catalog only
SQL Query
WITH simple_pk AS (SELECT
n.nspname AS table_schema,
c.relname as table_name,
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE) AS column_name
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND cardinality(o.conkey)=1
AND o.contype = 'p'
AND c.relkind = 'r'
),
simple_fk AS (SELECT
n.nspname AS table_schema,
c.relname as table_name,
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE) AS column_name
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND cardinality(o.conkey)=1
AND o.contype = 'f'
AND c.relkind = 'r'
)
SELECT table_schema, table_name, column_name
FROM simple_pk
WHERE column_name NOT ILIKE '%' || table_name || '%'
AND NOT EXISTS (SELECT *
FROM simple_fk
WHERE simple_pk.table_schema=simple_fk.table_schema
AND simple_pk.table_name=simple_fk.table_name
AND simple_pk.column_name=simple_fk.column_name)
ORDER BY table_schema, table_name;
Collections
This query belongs to the following collections:
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.
Categories
This query is classified under the following categories:
Name
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.
Uniqueness
Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.