Goal 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)
Reliability High (Few or no false-positive results)
License MIT (opens in new tab)
Fixing Suggestion Drop the duplicate constraints.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
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.

Fix Action #1
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.

SQL Query to Generate FixDescription
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:

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 .

NameDescription
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

This query is classified under the following categories:

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.

NameDescription
Fatal problemsQueries of this category provide information about problems that render a part of a database unusable.
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.