This query identifies potential classifier (or reference) tables that contain a column populated by a sequence generator. In good database design, tables storing standard reference data should typically use natural keys rather than auto-incrementing surrogate keys.
Notes
The query identifies classifier tables based on the following heuristics: it finds base tables that have four or less columns, have at most one foreign key, and have a column that is associated with a sequence generator. The query considers both external and internal sequence generators. The query only includes tables that name indicates that it is a classifier (reference data) table and it is not a master data table. The query considers both names in Estonian and English.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Replace surroggate keys with natural keys. Each classifier has a code and this should be used as the primary key.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
WITH fk AS (SELECT
(SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS target_schema,
c.relname AS target_table
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'),
tables_with_sequences AS (SELECT A.table_schema, A.table_name
FROM information_schema.columns A LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
INNER JOIN information_schema.tables T
ON A.table_schema = T.table_schema
AND A.table_name = T.table_name
INNER JOIN information_schema.schemata S
ON A.table_schema=S.schema_name
WHERE (coalesce (A.column_default, d.domain_default) ILIKE '%nextval%' OR is_identity='YES')
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')),
classifiers AS (SELECT A.table_schema, A.table_name
FROM information_schema.tables A
INNER JOIN information_schema.schemata S
ON A.table_schema = S.schema_name
WHERE
A.table_type = 'BASE TABLE'
AND (A.table_schema = 'public' OR S.schema_owner <> 'postgres')
AND A.table_name ~* '(klassifikaator|tüüp|tyyp|type|liik|liigid|kind|seisund|staatus|status|state|kood|code|sõnastik|sonastik|dict|register|loend|nimekiri|seadistus|kategoori|categor|lookup|ref|enum|class)'
AND A.table_name !~* '^[_]*(osapool|party|klient|customer|client|isik|person|kasutaja|user|toode|product|artikkel|article|teenus|service|töötaja|tootaja|employee|partner|ettevõte|ettevote|company|firma|organisatsioon|organization|leping|contract|asukoht|location|aadress|address|projekt|project|osakond|department)[_]*$'
AND (SELECT Count(*) AS cnt FROM INFORMATION_SCHEMA.columns AS c
WHERE A.table_schema=c.table_schema
AND A.table_name=c.table_name)<=4
AND (SELECT Count(*) AS cnt FROM fk
WHERE A.table_schema=fk.target_schema
AND A.table_name=fk.target_table)<=1
)
SELECT
table_schema,
table_name
FROM classifiers
WHERE (table_schema, table_name) IN (SELECT table_schema, table_name
FROM tables_with_sequences)
ORDER BY table_schema, table_name;
Collections
This query belongs to the following collections:
Name
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 .
Categories
This query is classified under the following categories:
Name
Description
Classifier tables
Queries of this category provide information about registration of classifiers.
Result quality depends on names
Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.
Sequence generators
Queries of this category provide information about sequence generators and their usage.
Uniqueness
Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.