Completely overlapping foreign keys

Query 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 about the query:

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.

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 the duplicate constraints.

Data source:

INFORMATION_SCHEMA+system catalog

SQL query:

Click on query to copy it

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 for generating SQL statements that help us to fix the problem

SQL query	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.

SQL query

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 where the query belongs to

Collection name	Collection 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 .

Collection name

Collection 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 where the query belongs to

Category name	Category 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.

Category name

Category 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.