Primary Key vs Unique Constraint

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. :)

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , , . Bookmark the permalink.

12 Responses to Primary Key vs Unique Constraint

  1. 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…

  2. Jason says:

    Transactional replication :)

  3. Damon Clark says:

    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.

  4. SQLDenis says:

    >>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)

  5. I thought of two more things that need “actual” PKs: XML indexes and FTS.

  6. Peter Cheung says:

    Change Tracking in SQL 2008 requires primary key.

  7. 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.

  8. 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! :)

  9. 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…

  10. ivanrdgz says:

    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.

  11. Sanjay Susheelan says:

    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

  12. Primary Key vs Unique Constraint SQL Fool modesty2013 – modesty2013 http://blog.libero.it/modesty2013/

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>