The list of all the queries

Foreign key columns that are associated with a sequence generator

Query goal: Find foreign key columns that are associated with a sequence generator. Foreign key values are selected amongst the values that are registered as corresponding primary key/unique key values. Values in the foreign key columns are not directly generated by the system. These values might be system generated indirectly - generated when a row is added to the primary (parent) table.
Notes about the query: The query takes into account both external and internal (are created as the result of declaring a column as the identity column) sequence generators. The query takes into account a possibility that a column may be associated with a sequence generator through a domain.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: High (Few or no false-positive results)
Query license: MIT License
Fixing suggestion: Drop foreign key column's association with a sequence generator.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH fk_columns 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),
columns AS (SELECT table_schema, table_name, column_name, CASE WHEN c.column_default IS NOT NULL OR c.is_identity='YES' THEN 'BASE TABLE' ELSE 'DOMAIN' END AS where_is_surrogate_implemented,
c.column_default,
d.domain_default,
c.is_identity
FROM INFORMATION_SCHEMA.columns c
LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
WHERE (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables 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 (coalesce (c.column_default, d.domain_default) ILIKE '%nextval%' OR c.is_identity='YES'))
SELECT table_schema, table_name, column_name, where_is_surrogate_implemented, column_default, domain_default, is_identity
FROM Columns
WHERE EXISTS (SELECT 1
FROM fk_columns
WHERE columns.table_schema=fk_columns.table_schema AND columns.table_name=fk_columns.table_name AND columns.column_name=fk_columns.column_name)
ORDER BY table_schema, table_name;

SQL statements for generating SQL statements that help us to fix the problem

SQL queryDescription
WITH fk_columns 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),
columns AS (SELECT table_schema, table_name, column_name
FROM INFORMATION_SCHEMA.columns c
WHERE (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables 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 c.column_default LIKE 'nextval%')
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I DROP DEFAULT;', table_schema, table_name, column_name) AS statements
FROM Columns
WHERE EXISTS (SELECT 1
FROM fk_columns
WHERE columns.table_schema=fk_columns.table_schema AND columns.table_name=fk_columns.table_name AND columns.column_name=fk_columns.column_name)
ORDER BY table_schema, table_name;
Drop the reference to an external sequence generator that is directly associated with the column.
WITH fk_columns 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),
columns AS (SELECT table_schema, table_name, column_name
FROM INFORMATION_SCHEMA.columns c
WHERE (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables 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 c.is_identity='YES')
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I DROP IDENTITY;', table_schema, table_name, column_name) AS statements
FROM Columns
WHERE EXISTS (SELECT 1
FROM fk_columns
WHERE columns.table_schema=fk_columns.table_schema AND columns.table_name=fk_columns.table_name AND columns.column_name=fk_columns.column_name)
ORDER BY table_schema, table_name;
Drop the specification that the foreign key column is also the identity column.
WITH fk_columns 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),
columns AS (SELECT table_schema, table_name, column_name, domain_schema, domain_name
FROM INFORMATION_SCHEMA.columns c
LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
WHERE (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables 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 d.domain_default LIKE 'nextval%')
SELECT DISTINCT format('ALTER DOMAIN %1$I.%2$I DROP DEFAULT;', domain_schema, domain_name) AS statements
FROM Columns
WHERE EXISTS (SELECT 1
FROM fk_columns
WHERE columns.table_schema=fk_columns.table_schema AND columns.table_name=fk_columns.table_name AND columns.column_name=fk_columns.column_name)
ORDER BY statements;
Drop the reference to an external sequence generator that is associated with the domain.

Collections where the query belongs to

Collection nameCollection description
Find problems about integrity constraintsA selection of queries that return information about the state of integrity constraints in the datadabase. 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
Default valueQueries of this catergory provide information about the use of default values.
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.
Sequence generatorsQueries of this category provide information about sequence generators and their usage.

The list of all the queries