Constraint Naming StandardA 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.
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:
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.
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.
Free eBookSubscribe 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.Read more Testimonials
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."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
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
The name Oracle is a trademark of Oracle Corporation.