Change referenced index for a Foreign Key
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
After reading MS DOCS here.
To modify a foreign key
To modify a FOREIGN KEY constraint by using Transact-SQL, you must first delete the existing FOREIGN KEY constraint and then re-create it with the new definition. For more information, see Delete Foreign Key Relationships and Create Foreign Key Relationships.
In you case I believe add a new FK and delete the old one. To disable scan you can use NO CHECK
option
--DROP TABLE T2
--DROP TABLE T1
CREATE TABLE T1 (
[Id] INT,
[NAME] varchar(100), CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED (id))
CREATE TABLE T2 (
t2_id int,
T1_Id INT NOT NULL
,CONSTRAINT [FK_T2_T1] FOREIGN KEY (T1_Id) REFERENCES T1(Id)
)
CREATE UNIQUE NONCLUSTERED INDEX IX_T1_Id ON T1 (Id)
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');
╔══════════╦════════════╦═════════════════╦══════════╗
║ index_id ║ index_name ║ index_type_desc ║ fk_name ║
╠══════════╬════════════╬═════════════════╬══════════╣
║ 1 ║ PK_T1 ║ CLUSTERED ║ FK_T2_T1 ║
║ 2 ║ IX_T1_Id ║ NONCLUSTERED ║ NULL ║
╚══════════╩════════════╩═════════════════╩══════════╝
ALTER TABLE T2
WITH NOCHECK
ADD CONSTRAINT [FK_T2_T1_NEW] FOREIGN KEY(T1_Id)
REFERENCES T1(Id)
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');
╔══════════╦════════════╦═════════════════╦══════════════╗
║ index_id ║ index_name ║ index_type_desc ║ fk_name ║
╠══════════╬════════════╬═════════════════╬══════════════╣
║ 1 ║ PK_T1 ║ CLUSTERED ║ FK_T2_T1 ║
║ 2 ║ IX_T1_Id ║ NONCLUSTERED ║ FK_T2_T1_NEW ║
╚══════════╩════════════╩═════════════════╩══════════════╝
ALTER TABLE T2
DROP CONSTRAINT FK_T2_T1
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');
╔══════════╦════════════╦═════════════════╦══════════════╗
║ index_id ║ index_name ║ index_type_desc ║ fk_name ║
╠══════════╬════════════╬═════════════════╬══════════════╣
║ 1 ║ PK_T1 ║ CLUSTERED ║ NULL ║
║ 2 ║ IX_T1_Id ║ NONCLUSTERED ║ FK_T2_T1_NEW ║
╚══════════╩════════════╩═════════════════╩══════════════╝
See if this works, what I am trying is to add one more FK so the new one is linked to the new index created and drop the old FK. I know the question is not to drop the existing one but see if this option will help you.
Also, as per the comments from Max Vernon:"the WITH NOCHECK option will prevent the foreign key being trusted by the optimizer. At some point, you'd have to alter the foreign key so that it is trusted using ALTER TABLE ... WITH CHECK"
The NOCHECK
will only be ignored at the time of creation but to enforce integrity contraint you have run this at some point of time.