Find completely overlapping foreign keys, i.e., the same set of columns of a table is covered by more than one foreign key constraint. These constraints could refer to the same table/key or different tables/keys.
Notes
The query assumes that the constraints cover the same set of columns and have the same order of columns. The query does not consider as to whether the constraints refer to the same table and key or not.
Type
Problem detection (Each row in the result could represent a flaw in the design)
WITH fk AS (SELECT table_schema, table_name, constraint_name, string_agg(column_name::text, ';' ORDER BY column_name) AS columns
FROM information_schema.key_column_usage AS kcu
WHERE (table_name, constraint_name) IN
(SELECT relname, conname
FROM pg_catalog.pg_constraint AS pco INNER JOIN pg_catalog.pg_class AS pcl ON
pco.conrelid=pcl.oid
WHERE contype='f')
GROUP BY table_schema, table_name, constraint_name)
SELECT table_schema, table_name, columns, Count(*) AS number_of_fk_constraints
FROM fk
GROUP BY table_schema, table_name, columns
HAVING Count(*)>1
ORDER BY table_schema, table_name;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
WITH fk AS (SELECT table_schema, table_name, constraint_name, string_agg(column_name::text, ';' ORDER BY column_name) AS columns
FROM information_schema.key_column_usage AS kcu
WHERE (table_name, constraint_name) IN
(SELECT relname, conname
FROM pg_catalog.pg_constraint AS pco INNER JOIN pg_catalog.pg_class AS pcl ON
pco.conrelid=pcl.oid
WHERE contype='f')
GROUP BY table_schema, table_name, constraint_name),
duplicates AS (SELECT table_schema, table_name, columns, Count(*) AS number_of_fk_constraints
FROM fk
GROUP BY table_schema, table_name, columns
HAVING Count(*)>1)
SELECT format('ALTER TABLE %1$I.%2$I DROP CONSTRAINT %3$I;', table_schema, table_name, constraint_name) AS statements
FROM fk
WHERE (table_schema, table_name, columns) IN
(SELECT table_schema, table_name, columns
FROM duplicates)
ORDER BY table_schema, table_name;
Drop the foreign key constraint.
Collections
This query belongs to the following collections:
Name
Description
Find problems about integrity constraints
A 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 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
Fatal problems
Queries of this category provide information about problems that render a part of a database unusable.
Relationships between tables
Queries 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.