| 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:
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 |
|
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 .
| Name | Description |
|---|---|
| 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
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.
| 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. |
Further reading and related materials: