Constraint Naming Standard

By correctly naming all constraints, we can quickly associate a particular constraint with our data model. This gives us two real advantages:

1. We can quickly identify and fix any errors.
2. We can reliably modify or drop constraints.

The basic format a constraint naming standard could be:

(table name)_(column_name)_(constraint abbreviation)

If the constraint name is still too long, you should consider rewriting your entire data model. Constraint naming Standards for Primary key constraints follow this naming convention:

1. PK_xxxxx, Where xxxxx = The table name that the index is built on.
2. UK_xxxxx_nn, Where xxxxx = The table name that the index is built on, and nn = A number that makes the constraint unique.
3. FK_pppp_cccc_nn, Where pppp = The parent table name and cccc = The child parent table name and nn = A number that makes the constraint unique.
4. All constraints must be named; default SYS_ names are not acceptable.
5. Primary, Unique and Check constraints are to be named

   TableName_MainColumnName[Number]_ConstraintTypeCode
   TableAlias_MainColumnName[Number]_ConstraintTypeCode

   where MainColumnName identifies the column or main column to which the constraint applies.

   TableName / TableAlias identifies either the name or the alias of the table in which the column is the primary key or is typically maintained and where Number is an optional field used to resolve duplicate index names.

   The initial index would not have a number. Additional indexes would start at the number 1 and ConstraintTypeCode is one of the following:

   pk to indicate a primary key constraint
   ck to indicate a check condition constraint
   uk to indicate a unique key constraint

6. Foreign Key constraints are to be named

   TableName_MainColumnName_TableName_ConstraintTypeCode
   TableAlias_MainColumnName_Table Alias_ConstraintTypeCode
   where TableName / TableAlias identifies either the name or the alias of the table in which the column is the primary key or is typically maintained where MainColumnName is optional and identifies the column or main column to which the constraint applies.

   This component of the name should be used only if it is necessary to uniquely identify the constraint in cases where there is more than one foreign key relationship between two tables.

   TableName / TableAlias identifies either the name or the alias of the table in which the column is referenced and where ConstraintTypeCode is one of the following:

   fk to indicate a foreign key constraint
   pk to indicate a primary key constraint

7. A constraint name must be unique within the table to which it pertains.

There are lots of arguments on whether or not we should be naming not null constraints. So, if you want to name them, please do so and follow the above naming standard.

But, naming not null constraints is not a requirement. Though naming "not null" constraints doesn't help immediately in error debugging (e.g. the error will say something like "Cannot insert null value into column"), I recommend naming not null constraints to be consistent in our naming of all constraints.

• If you have to abbreviate the table name for one of the constraints, abbreviate it for all the constraints
• If you are defining a multi column constraint, try to truncate the two column names evenly

Rules for a constraint naming standard:

Rule 1: All integrity constraints except NOT NULL constraints must be explicitly named.

Rule 2: Single-column primary key constraints will be named TableName_PK_ColumnName.

Rule 3: Multiple-column primary key constraints will be named.

TableName_PKn where n is the number of columns in the primary key.

Rule 4: Single-column unique constraints will be named TableName_U_ColumnName.

Rule 5: Multiple-column unique constraints will be named TableName_Un[_p] where n is the number of columns in the index and p is an optional sequence number to uniquely identify several unique constraints with the same number of columns.

Rule 6: Single-column foreign key constraints will be named TableName_FK_ColumnName.

Rule 7: Multiple-column foreign key constraints will be named TableName_FKn[_p] where n is the number of columns in the index and p is an optional sequence number to uniquely identify several foreign keys with the same number of columns.

Rule 8: CHECK constraints will be named TableName_CK_ColumnName .

Rule 9: If they are named (not recommended) NOT NULL constraints will be named TableName_NN_ColumnName.

I hope you will find these Constraint naming standards useful.

Return to Oracle Database constraints