Systems Engineering and RDBMS

Difference between Unique Constraint and Unique Index

Posted by decipherinfosys on December 21, 2007

This has been brought up a couple of times at the client sites so we decided to put some comments on this topic. A unique constraint gets implemented using a unique index so from that perspective there is no difference between the two. The syntax is different for the two.

However, one thing that you need to be aware of is that the index creation options (other than some of them like the FILLFACTOR (SQL Server)) that you have at your disposal when enforcing this using a unique index are not available to you when you use the unique constraint syntax. One can always change it later on if one so wishes – our personal preference is to use the “CREATE INDEX” statement to enforce this.

If you want to read up on the difference between a Primary Key and a Unique Index, you can read it in one of our previous blog post here. And here’s another post detailing how one can handle multiple NULL values in a unique index in SQL Server and DB2 LUW.

About these ads

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 79 other followers

%d bloggers like this: