Find possible classifier tables that have a column with a sequence generator. Such tables should have natural keys instead of surrogate keys.
Notes
The query finds base tables that have four or less columns do not have any foreign keys and have a column that is associated with a sequence generator. The query considers both external and internal sequence generators.
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'))
SELECT
table_schema,
table_name
FROM information_schema.tables AS t
WHERE table_type='BASE TABLE'
AND table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
AND (SELECT Count(*) AS cnt FROM INFORMATION_SCHEMA.columns AS c
WHERE t.table_schema=c.table_schema
AND t.table_name=c.table_name)<=4
AND (table_schema, table_name) NOT IN (SELECT target_schema, target_table
FROM fk)
AND (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.
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.