Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am working with a legacy database that was imported from MS Access. There are about twenty tables with non-clustered, unique primary keys that were created during the MS Access > SQL Server upgrade.

Many of these tables also have unique, non-clustered indexes that are duplicates of the primary key.

I am attempting to clean this up.

But what I have found is after I recreate the primary keys as clustered indexes, and then try to rebuild the foreign key, the foreign key is referencing the old, duplicate index (which was unique).

I know this because it won't let me drop the duplicate indexes.

I would think SQL Server would always choose a primary key if one existed. Does SQL Server have a method of choosing between a unique index and a primary key?

To duplicate the problem (on SQL Server 2008 R2):

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Child') DROP TABLE Child
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Parent') DROP TABLE Parent
GO

-- Create the parent table
CREATE TABLE Parent (ParentID INT NOT NULL IDENTITY(1,1)) 

-- Make the parent table a heap
ALTER TABLE Parent ADD CONSTRAINT PK_Parent PRIMARY KEY NONCLUSTERED (ParentID) 

-- Create the duplicate index on the parent table
CREATE UNIQUE NONCLUSTERED INDEX IX_Parent ON Parent (ParentID) 

-- Create the child table
CREATE TABLE Child  (ChildID  INT NOT NULL IDENTITY(1,1), ParentID INT NOT NULL ) 

-- Give the child table a normal PKey
ALTER TABLE Child ADD CONSTRAINT PK_Child PRIMARY KEY CLUSTERED (ChildID) 

-- Create a foreign key relationship with the Parent table on ParentID
ALTER TABLE Child ADD CONSTRAINT FK_Child FOREIGN KEY (ParentID) 
REFERENCES Parent (ParentID) ON DELETE CASCADE NOT FOR REPLICATION

-- Try to clean this up
-- Drop the foreign key constraint on the Child table
ALTER TABLE Child DROP CONSTRAINT FK_Child

-- Drop the primary key constraint on the Parent table
ALTER TABLE Parent DROP CONSTRAINT PK_Parent

-- Recreate the primary key on Parent as a clustered index
ALTER TABLE Parent ADD CONSTRAINT PK_Parent PRIMARY KEY CLUSTERED (ParentID) 

-- Recreate the foreign key in Child pointing to parent ID
ALTER TABLE Child ADD CONSTRAINT FK_Child FOREIGN KEY (ParentID) 
REFERENCES Parent (ParentID) ON DELETE CASCADE NOT FOR REPLICATION

-- Try to drop the duplicate index on Parent 
DROP INDEX IX_Parent ON Parent 

Error msg:

Msg 3723, Level 16, State 6, Line 36 An explicit DROP INDEX is not allowed on index 'Parent.IX_Parent'. It is being used for FOREIGN KEY constraint enforcement.

share|improve this question
    
The nonclustered index is much smaller: it only contains the primary key. It doesn't even need a reference to the clustered index because it contains the clustered key! Because it's much faster to read a small index into memory, SQL Server will prefer the smallest key for foreign key enforcement. –  Andomar Jun 29 '12 at 0:11
    
I may not be understanding, but if I create primary key on the table as a clustered index in the example above, it will not use the duplicate index key as a reference (and the duplicate can be deleted). This appears to be related to it being a heap first... –  8kb Jun 29 '12 at 0:15
    
The choice is made when you create the foreign key. If the smaller non-clustered index is available at that point, that's what will be used. The table doesn't even remember that it used to be a heap, so I would not expect that to have any influence. –  Andomar Jun 29 '12 at 0:19
    
It makes sense...but the behavior is only on SQL Server 2008 R2 (not 2005, it's a complicated mess how I know all this), so I'm still leaning towards "potential bug." –  8kb Jun 29 '12 at 0:23
    
SQL Server might choose a different one depending on SQL Server version. It may also choose a different one based on the time of day. That would not count as a bug, because SQL Server makes no guarantees about how it will resolve the choice at all :) –  Andomar Jun 29 '12 at 0:25
show 4 more comments

migrated from stackoverflow.com Jun 29 '12 at 16:38

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 3 down vote accepted

The (lack of) documentation suggests that this behaviour is an implementation detail, and is therefore undefined and subject to change at any time.

This is in stark contrast to CREATE FULLTEXT INDEX, where you have to specify the name of an index to attach to -- AFAIK, there is no undocumented FOREIGN KEY syntax to do the equivalent (though theoretically, there could be in the future).

As mentioned, it does make sense that SQL Server chooses the smallest physical index with which to associate the foreign key. If you change the script to create the unique constraint as CLUSTERED, the script "works" on 2008 R2. But that behaviour is still undefined and should not be relied upon.

As with most legacy applications, you'll just have to get down to the nitty-gritty and clean things up.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.