Goal Find names of foreign key constraints that are used within the same schema more than once. Find names of check constraints that are used within the same schema more than once. Find names of constraint triggers that are used within the same schema more than once. 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. It is entirely normal that a domain has a check constraint and the domain is used in case of multiple columns. The query considers only locally-defined constraints, i.e., constraints that are not inherited. 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)
Reliability High (Few or no false-positive results)
License MIT (opens in new tab)
Fixing Suggestion Give to foreign key and check constraints as well as to constraint triggers unique names within a schema. Use table name in the constraint name. Follow a naming convention.
Data Source system catalog only
SQL Query 
with constraints as (select distinct
o.conname, 
(select nspname from pg_namespace where oid=c.relnamespace) as table_schema,
c.relname as table_name,
case when o.contype='c' then 'CHECK'
when o.contype='f' then 'FOREIGN KEY' 
when contype='t' THEN 'CONSTRAINT TRIGGER'
end as constraint_type,
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_class c on c.oid = o.conrelid
where c.relkind in ('r','f') 
and o.contype in ('f', 'c')
and o.conislocal=true)
select table_schema, constraint_type, conname, count(*) AS number_of_occurrences, string_agg(table_type || '.' || table_schema || '.' || table_name, ';<br>' order by table_schema, table_name) as tables
from constraints
group by table_schema, constraint_type, conname
having count(*)>1
order by count(*) desc, table_schema, constraint_type;

Collections

This query belongs to the following collections:

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 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:

Comfortability of database evolution

Queries of this category provide information about the means that influence database evolution.

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.

NameDescription
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
NamingQueries of this category provide information about the style of naming.
UniquenessQueries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.