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