How does SQL Server choose an index key for a foreign key reference?

Question

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.

Answer 1

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.

Answer 2

Does SQL Server have a method of choosing between a unique index and a primary key?

At least it is possible to direct SqlServer to reference primary key, when foreign key is being created and alternative key constraints or unique indexes do exist on the table being referenced.

If primary key needs to be referenced, then only name of the table being referenced should be specified in the foreign key definition and list of columns being referenced should be omitted:

ALTER TABLE Child
    ADD CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentID)
        -- omit key columns of the referenced table
        REFERENCES Parent /*(ParentID)*/;

More details below.

---

Consider the following setup:

CREATE TABLE T (id int NOT NULL, a int, b int, c uniqueidentifier, filler binary(1000));
CREATE TABLE TRef (tid int NULL);

where table TRef intends to reference table T.

To create referential constraint one can use ALTER TABLE command with two alternatives:

ALTER TABLE TRef
    ADD CONSTRAINT FK_TRef_T_1 FOREIGN KEY (tid) REFERENCES T (id);

ALTER TABLE TRef
    ADD CONSTRAINT FK_TRef_T_2 FOREIGN KEY (tid) REFERENCES T;

notice that in the second case no columns of the table being referenced are specified (REFERENCES T versus REFERENCES T (id)).

Since there are no key indexes on T yet, execution of these commands will generate errors.

First command returns following error:

Msg 1776, Level 16, State 0, Line 4

There are no primary or candidate keys in the referenced table 'T' that match the referencing column list in the foreign key 'FK_TRef_T_1'.

The second command, however, returns different error:

Msg 1773, Level 16, State 0, Line 4

Foreign key 'FK_TRef_T_2' has implicit reference to object 'T' which does not have a primary key defined on it.

see that in the first case expectation is primary or candidate keys, whereas in the second case expectation is primary key only.

Let's check if SqlServer will use something other than primary key with the second command or not.

If we add some unique indexes and unique key on T:

CREATE UNIQUE INDEX IX_T_1 on T(id) INCLUDE (filler);
CREATE UNIQUE INDEX IX_T_2 on T(id) INCLUDE (c);
CREATE UNIQUE INDEX IX_T_3 ON T(id) INCLUDE (a, b);

ALTER TABLE T
    ADD CONSTRAINT UQ_T UNIQUE CLUSTERED (id);

command for FK_TRef_T_1 creation succeeds, but command for FK_TRef_T_2 creation still fails with Msg 1773.

Finally, if we add primary key on T:

ALTER TABLE T
    ADD CONSTRAINT PK_T PRIMARY KEY NONCLUSTERED (id);

command for FK_TRef_T_2 creation succeeds.

Let's check what indexes of the table T are referenced by foreign keys of table TRef:

select
    ix.index_id,
    ix.name as index_name,
    ix.type_desc as index_type_desc,
    fk.name as fk_name
from sys.indexes ix
    left join sys.foreign_keys fk on
        fk.referenced_object_id = ix.object_id
        and fk.key_index_id = ix.index_id
        and fk.parent_object_id = object_id('TRef')
where ix.object_id = object_id('T');

this returns:

index_id  index_name  index_type_desc   fk_name
--------- ----------- ----------------- ------------
1         UQ_T        CLUSTERED         NULL
2         IX_T_1      NONCLUSTERED      FK_TRef_T_1
3         IX_T_2      NONCLUSTERED      NULL
4         IX_T_3      NONCLUSTERED      NULL
5         PK_T        NONCLUSTERED      FK_TRef_T_2

see that FK_TRef_T_2 correspond to PK_T.

So, yes, with use of REFERENCES T syntax foreign key of TRef is mapped to primary key of T.

I was not able to find such behavior described in SqlServer documentation directly, but dedicated Msg 1773 suggests that it is not accidental. Likely such implementation provides compliance with the SQL Standard, below is short excerpt from section 11.8 of ANSI/ISO 9075-2:2003

11 Schema definition and manipulation

11.8 <referential constraint definition>

Function
Specify a referential constraint.

Format

<referential constraint definition> ::=
    FOREIGN KEY <left paren> <referencing columns> <right paren>
        <references specification>

<references specification> ::=
    REFERENCES <referenced table and columns>
    [ MATCH <match type> ]
    [ <referential triggered action> ]
...

Syntax Rules
...
3) Case:
...
b) If the <referenced table and columns> does not specify a <reference column list>, then the table descriptor of the referenced table shall include a unique constraint that specifies PRIMARY KEY. Let referenced columns be the column or columns identified by the unique columns in that unique constraint and let referenced column be one such column. The <referenced table and columns> shall be considered to implicitly specify a <reference column list> that is identical to that <unique column list>.
...

Transact-SQL supports and extends ANSI SQL. It does not conform to SQL Standard exactly however. There is a document named SQL Server Transact-SQL ISO/IEC 9075-2 Standards Support Document (MS-TSQLISO02 in short, see here) describing level of support that is provided by Transact-SQL. The document lists extensions and variations to standard. For example it documents that MATCH clause is not supported in the referential constraint definition. But there are no documented variations relevant to cited piece of standard. So, my opinion is that observed behavior is documented enough.

And with use of REFERENCES T (<reference column list>) syntax it seems that SqlServer selects first suitable nonclustered index among the indexes of table being referenced (the one with the least index_id seemingly, not the one with the smallest physical size as assumed in the question comments), or clustered index if it suits and there are no suitable nonclustered indexes. Such behavior seems to be consistent since SqlServer 2008 (version 10.0). This is just observation of course, no guarantees in this case.