Search This Blog

Monday, December 31, 2007

Difference between a Primary Key and a Unique Index

The differences between a Primary Key and a Unique Index are:
1) Column(s) that make the Primary Key of a table cannot be NULL since by definition, the Primary Key cannot be NULL since it helps uniquely identify the record in the table. The column(s) that make up the unique index can be nullable. A note worth mentioning over here is that different RDBMS treat this differently –> while SQL Server and DB2 do not allow more than one NULL value in a unique index column, Oracle allows multiple NULL values. That is one of the things to look out for when designing/developing/porting applications across RDBMS.
2) There can be only one Primary Key defined on the table where as you can have many unique indexes defined on the table (if needed).
3) Also, in the case of SQL Server, if you go with the default options then a Primary Key is created as a clustered index while the unique index (constraint) is created as a non-clustered index.
This is just the default behavior though and can be changed at creation time, if needed.
So, if the unique index is defined on not null column(s), then it is essentially the same as the Primary Key and can be treated as an alternate key meaning it can also serve the purpose of identifying a record uniquely in the table.

I have directly picked the above information from http://decipherinfosys.wordpress.com/2007/07/04/back-to-the-basics-difference-between-primary-key-and-unique-index/ link.

1 comment:

  1. One more thing. Primary keys semantically represent unique identity of each row. Unique index + NOT NULL can be used as an alternate to PKs, but this again would defeat the whole point of having a PK. Although a PK and UK + NOT NULL do the same job, primary keys will be used by the optimizer to make some decisions. So its better not to use alternate ways of declaring primary keys.

    ReplyDelete