Goal This query identifies table constraints (CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE, and EXCLUDE) that share the exact same name across multiple base or foreign tables. According to the SQL standard, constraint names must be unique within a schema. However, PostgreSQL only enforces constraint name uniqueness at the table level for CHECK and FOREIGN KEY constraints (and at the schema level for index-backed constraints). Allowing multiple tables to use identical constraint names causes several issues:

  • Broken INFORMATION_SCHEMA queries: Standard INFORMATION_SCHEMA views (such as check_constraints, referential_constraints, and constraint_column_usage) assume that the pair (constraint_schema, constraint_name) uniquely identifies a constraint. Duplicate names within a schema cause joins across these views to produce Cartesian products and inaccurate metadata results.
  • Ambiguous error handling and logging: When a constraint violation occurs, application error handlers, ORMs, and database administrators relying on the constraint name cannot immediately determine which table caused the failure without inspecting additional context.

Constraints that are inherited by or redefined on child tables (INHERITS or declarative partitions) with the same name as on their parent table are excluded, as PostgreSQL table inheritance naturally propagates parent constraint names to child relations.
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
Reliability Medium Medium number of false-positive results
License MIT (opens in new tab)
Data Source system catalog only
SQL Query
WITH constraints AS (
    SELECT 
        o.conname, 
        n.nspname AS table_schema, 
        c.relname AS table_name,
        CASE 
            WHEN o.contype = 'c' THEN 'CHECK'
            WHEN o.contype = 'f' THEN 'FOREIGN KEY'
            WHEN o.contype = 'p' THEN 'PRIMARY KEY'
            WHEN o.contype = 'u' THEN 'UNIQUE'
            WHEN o.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'      
      AND o.coninhcount = 0      
      AND NOT EXISTS (
          SELECT 1
          FROM pg_inherits i
          INNER JOIN pg_constraint parent_con ON parent_con.conrelid = i.inhparent
          WHERE i.inhrelid = c.oid
            AND parent_con.conname = o.conname
      )
)
SELECT 
    conname, 
    Count(*) AS number_of_appearances, 
    string_agg(
        table_type || '.' || table_schema || '.' || table_name || ' (' || contype || ')',
        ';<br>' ORDER BY table_schema, table_name, contype
    ) AS tables
FROM constraints
GROUP BY conname
HAVING Count(*) > 1
ORDER BY Count(*) DESC;

Fixing Suggestion

Rename the conflicting constraints (ALTER TABLE RENAME CONSTRAINT TO ;) so that each constraint has a unique name across the database. Follow a consistent naming convention that incorporates the table name, such as pk_, ak__, fk__, and chk__.

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.

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.

NameDescription
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
Duplication of implementation elementsQueries of this catergory provide information about the duplication of the database objects.
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.