| Goal | This query identifies database constraints that do not follow standard naming conventions, specifically those that fail to incorporate either the parent table's full name or an abbreviation formed from the initial letters of its name components (e.g., an acronym). Embedding the table's identity into the constraint name is crucial: it ensures schema-wide uniqueness and provides clear, immediate context when constraint violations are logged or surfaced to end-users. |
|---|---|
| Notes | The query considers only locally-defined constraints, i.e., constraints that are not inherited. The query considers a possibility that underscores (_) in the table name have been removed from the constraint/index name. |
| 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
Use the table name in the constraint names and index names. A problem is that by default a PostgreSQL identifier may be at most 63 bytes long. Thus, in case of long table names one may have to shorten the name in the constraint or index names. It is important to do this consistently. Do not shorten the table name. Rename the flagged constraint using ALTER TABLE ... RENAME CONSTRAINT to include either the full table name or its approved initialism.
Collections
This query belongs to the following collections:
Find problems about names
A selection of queries that return information about the names of database objects. 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 .
| Name | Description |
|---|---|
| Find problems about names | A selection of queries that return information about the names of database objects. 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
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.
| Name | Description |
|---|---|
| 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. |
Further reading and related materials: