9

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!

1
  • There was a relevant discussion here.
    – i-one
    Commented Sep 17, 2019 at 20:57

2 Answers 2

6

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

3

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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