The Database Design Resource Center



Constraint Naming Standard

A constraint naming standard are important for many reasons, but one of the most important reason is that the SYS_* name oracle assigns to unnamed constraints is not very understandable.

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


Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant


Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:


What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials



Database Normalization eBook:


Database Normalization eBook




Copyright © www.databasedesign-resource.com /
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.