Find base table and foreign table constraint names that are used in a database more than once (possibly in different schemas or in case of different types of constraints). Different things should have different names. But here different constraints have the same name. Also make sure that this is not a sign of duplication.
Notes
In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser. The query considers only constraints that are connected directly with a base table or a foreign table, i.e., it does not consider domain CHECK constraints. The query does not consider constraint triggers becasuse these could be used to implement foreign key constraints and the same name could be used in case of different triggers that implement the same constraint.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Rename the constraints so that the name is unique within a schema. Follow a naming convention.
Data Source
system catalog only
SQL Query
WITH constraints AS (SELECT conname, nspname AS table_schema,
c.relname as table_name,
CASE
WHEN contype='c' THEN 'CHECK'
WHEN contype='f' THEN 'FOREIGN KEY'
WHEN contype='p' THEN 'PRIMARY KEY'
WHEN contype='u' THEN 'UNIQUE'
WHEN contype='x' THEN 'EXCLUDE'
END AS contype,
CASE WHEN c.relkind='r' then 'BASE TABLE'
WHEN c.relkind='f' then 'FOREIGN TABLE'
END AS table_type
FROM pg_constraint o INNER JOIN pg_namespace n ON o.connamespace=n.oid
INNER JOIN pg_class c on c.oid = o.conrelid
WHERE (substring(pg_get_constraintdef(o.oid),7) NOT LIKE '%NOT NULL' OR substring(pg_get_constraintdef(o.oid),7) IS NULL)
AND c.relkind IN ('r', 'f')
AND o.contype<>'t')
SELECT conname, Count(*) AS number_of_appearances, string_agg(table_type || '.' || table_schema || '.' || table_name || ' (' || contype || ')','; ' ORDER BY table_schema, table_name, contype) AS tables
FROM constraints
GROUP BY conname
HAVING Count(*)>1
ORDER BY Count(*) DESC;
Categories
This query is classified under the following categories:
Name
Description
Comfortability of database evolution
Queries of this category provide information about the means that influence database evolution.
Duplication of implementation elements
Queries of this catergory provide information about the duplication of the database objects.
Naming
Queries of this category provide information about the style of naming.
Uniqueness
Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.