Recently, I encountered a table that needed to have the definition of a clustered index altered. It just so happens that the clustered index and the primary key were one and the same, a pretty common occurrence. However, when we went to modify the index, it failed.
The following entry in Books Online for CREATE INDEX explains why:
If the index enforces a PRIMARY KEY or UNIQUE constraint and the index definition is not altered in any way, the index is dropped and re-created preserving the existing constraint. However, if the index definition is altered the statement fails. To change the definition of a PRIMARY KEY or UNIQUE constraint, drop the constraint and add a constraint with the new definition.
Let’s test this, shall we?
/* Create a table with a clustered primary key */ Create Table dbo.myTable ( myID int identity(1,1) Not Null , myDate smalldatetime Not Null , myNumber int Not Null Constraint CIX_myTable Primary Key Clustered (myDate, myID) ); /* Insert some data */ Insert Into myTable Select '2009-01-01', 100 Union All Select '2009-02-01', 200 Union All Select '2009-01-05', 300; /* Try to alter the index - FAIL */ Create Clustered Index CIX_myTable On myTable(myID, myDate, myNumber) With (Drop_Existing = On); /* Drop the clustered primary key */ Alter Table dbo.myTable Drop Constraint CIX_myTable; /* Add a unique clustered index */ Create Unique Clustered Index CIX_myTable On myTable(myDate, myID); /* Add a unique constraint */ Alter Table myTable Add Constraint Unique_myTable Unique (myDate); /* Try to alter the index - SUCCESS */ Create Clustered Index CIX_myTable On myTable(myID, myDate, myNumber) With (Drop_Existing = On); /* Add a primary key constraint */ Alter Table myTable Add Constraint PK_myTable Primary Key (myID, myDate); /* Try to alter the index - SUCCESS */ Create Clustered Index CIX_myTable On myTable(myID, myDate) With (Drop_Existing = On); /* Clean-Up */ Drop Table myTable; |
The only instance that actually fails is the PRIMARY KEY constraint. The unique clustered index is able to be modified successfully, even when a unique constraint is applied to the table. So either I’m misunderstanding BOL, or BOL is mistaken. Either way, I’m then left with the following question: is there any reason to actually use a primary key when a unique index serves the same purpose and offers greater flexibility?
Questions, comments, and explanations are welcome. 
The PK shows up in the UI with nice little key icons, whereas the UNIQUE index doesn’t. And that’s about the only benefit I can think of. You can also do other things with UNIQUE indexes, e.g. create them non-clustered and use INCLUDE, if you want to use a non-clustered key and want to avoid bookmark lookups at the same time…
Transactional replication
Assuming you are not allowing nulls in those columns, there is not a lot of difference. As Adam said, for diagramming, etc it won’t be as clear because most tools (SSMS, SQL Diagrams, virtually any modeling tool) are designed to show PKs and FKs in a special manner.
>>is there any reason to actually use a primary key when a unique index serves the same purpose and offers greater flexibility?
A primary key does not allow nulls while a unique index allows 1 null, this might be problematic if you are trying to create a foreign key
Also by default a PK is clustered while a unique constraint is not
People just have to be aware of the differences…hey for all you know someone puts 2 unique constraints on the same column on sorted desc the other asc (I have seen this!!!)
create table bla(id int)
alter table bla add constraint uq_con unique (id asc)
alter table bla add constraint uq_con2 unique (id desc)
I thought of two more things that need “actual” PKs: XML indexes and FTS.
Change Tracking in SQL 2008 requires primary key.
I believe that a unique index rather than a Clustered Primary Key will probably lead to bookmark lookups rather than Index seeks, and can radically increases the disk I/O. Where there is no primary key but a unique clustered index these should be altered to be a Clustered Primary Key.
I’m just now getting a chance to catch up to comments…
@Jason – how does that affect transactional replication? I’m a replication novice.
@Damon – I don’t care about diagrams.
@SQLDenis – good point about the nullability!
@Adam – why do XML indexes require PK’s? I’m not familiar with XML indexes.
@Carolyn – I’m not sure why the DB engine would treat a clustered unique index any different than a clustered primary key. Could you provide an example or link to more information?
Thanks everyone for your comments and input!
Clustered unique constraint on non-nullable columns will behave exactly the same as clustered PK with regard to the various components of the query engine.
XML indexes require PKs because under the covers a hidden table is created (called the “nodes table”) which has a 1:m relationship with the original table. The nodes table includes the PK from the original table so that if a query can perform best by doing a seek into the nodes table first, then finding related attributes in the base table, that can be done as efficiently as possible. The requirement for PK vs unique is probably just one of ease, so they don’t have to figure out which unique constraint/index to use if there are multiple…
Michelle,
In order to replicate a table using transactional replication the table must have a primary key. The replication uses the primary key for tracking all changes on the published database and the subscriber. In fact, if you modify the primary key on the subscriber (I am talking about simple transactional replication) and afterward you modify the same record on the publisher the replication will fail, since it will not be able to find that record on the subscriber. In merge replication a rowguid column is added to each published table for that purpose.
Both works at different LEVEL (Logical Level/Physical Level/Conceptual Level).
Constraints operates at Logical Level of DATA, whereas Index constraints operates at Physical Level of DATA.
Integrity Constrainsts are checked well before the actual DATA INSERTION take place..
&
on the other hand Index uniqueness is checked at the time of actually data been written to index.
And the important thing is, all Logical Level objects (Constraints) are treated as Relational database objects and can participate in Referential Integrity.
Regards
Sanjay
Primary Key vs Unique Constraint SQL Fool modesty2013 – modesty2013 http://blog.libero.it/modesty2013/