Primary Key vs Unique Constraint

June 19, 2009 by Michelle Ufford · 10 Comments
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: , , , ,

BOL 2008 Update Released

May 22, 2009 by Michelle Ufford · 3 Comments
Filed under: SQL 2008, Syndication 

If you haven’t heard, Microsoft released an update to Books Online for SQL Server 2008 yesterday. You can find the download here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=765433f7-0983-4d7a-b628-0a98145bcb97&displaylang=en

Tags: , , ,