HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Change referenced index for a Foreign Key

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
referencedforeignforindexchangekey

Problem

I have something like this:

CREATE TABLE T1 (
    Id INT
    ...
    ,Constraint [PK_T1] PRIMARY KEY CLUSTERED [Id]
)

CREATE TABLE T2 (
    ....
    ,T1_Id INT NOT NULL
    ,CONSTRAINT [FK_T2_T1] FOREIGN KEY (T1_Id) REFERENCES T1(Id)
)


For performance (and deadlock) reasons I created a new index on T1

CREATE UNIQUE NONCLUSTERED INDEX IX_T1_Id ON T1 (Id)


But if I check to which Index references the FK, keeps referencing to the clustered index

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('T2')
where ix.object_id = object_id('T1');


If I drop the constraint and create again it references the nonclustered index, but this lead to check all t2 FK again.

Is there a way to change this so the FK_T2_T1 uses IX_T1_Id instead of PK_T1 without dropping the FK and locking the table on FK checking?

Thanks!

Solution

Well, after continue searching I found this article


Unlike a normal query, it won't pick up a new index due to statistics being updated, a new index being created, or even a server being rebooted. The only way I'm aware of to have a FK bind to a different index is to drop and recreate the FK, letting it automatically select the index with no options to control it manually.

Whereupon, unless someone can say otherwise, I will have to look for a time window to perform this task.

Thanks

Context

StackExchange Database Administrators Q#246381, answer score: 6

Revisions (0)

No revisions yet.