Primary Key vs Unique Constraint

June 19, 2009 by Michelle Ufford
Filed under: Miscellaneous, Syndication 

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

Tags: , , , ,

Comments

10 Comments on Primary Key vs Unique Constraint

  1. Adam Machanic on Fri, 19th Jun 2009 9:54 am

    2. 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 on Fri, 19th Jun 2009 10:09 am

    3. Transactional replication :)

  3. Damon Clark on Fri, 19th Jun 2009 10:15 am

    4. 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 on Fri, 19th Jun 2009 11:07 am

    5. >>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. Adam Machanic on Sun, 21st Jun 2009 7:23 am

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

  6. Peter Cheung on Mon, 22nd Jun 2009 1:22 am

    7. Change Tracking in SQL 2008 requires primary key.

  7. Carolyn Richardson on Mon, 22nd Jun 2009 8:42 am

    8. 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. Michelle Ufford on Mon, 22nd Jun 2009 8:59 am

    9. 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. Adam Machanic on Mon, 22nd Jun 2009 2:18 pm

    10. 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 on Sat, 17th Oct 2009 8:12 pm

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

Tell me what you're thinking...
and oh, if you've enjoyed the post, please consider subscribing to my blog.