Systems Engineering and RDBMS

Back to the Basics: Difference between Primary Key and Unique Index

Posted by decipherinfosys on July 4, 2007

Here is another post in the back to the basics section: What is the difference between a Primary Key and a Unique Index? Both can be declared on one or more columns, both can be used to enforce foreign keys (if the unique index is on not null column(s)), both can be declared as clustered/non clustered indexes (SQL Server lingo), both can be used on computed columns as well (SQL Server).

The differences between the two 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.

Other posts in the Back to the Basics Section:

Foreign Keys

Identity Inserts

About these ads

2 Responses to “Back to the Basics: Difference between Primary Key and Unique Index”

  1. [...] a stored procedureCOALESCE(), ISNULL(), NVL()Update data in one table with data from another tableBack to the Basics: Difference between Primary Key and Unique IndexExporting and Importing Virtual Machines Using ‘vmkfstools’Temporary tables – Oracle « [...]

  2. [...] 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 [...]

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 74 other followers

%d bloggers like this: