The use of multiple foreign keys on same column in SQL Server

SQL Server allows me to create multiple foreign keys on a column, and each time using just different name I can create another key referencing to the same object. Basically all the keys are defining the same relationship. I want to know what's the use of having multiple foreign keys which are defined on the same column and reference to the same column in another table. What's the benefit of it that SQL Server allows us to do a thing like that?

SQL Server allows you to do a lot of silly things.

You can even create a foreign key on a column referencing itself - despite the fact that this can never be violated as every row will meet the constraint on itself.

One edge case where the ability to create two foreign keys on the same relationship would be potentially useful is because the index used for validating foreign keys is determined at creation time. If a better (i.e. narrower) index comes along later then this would allow a new foreign key constraint to be created bound on the better index and then the original constraint dropped without having any gap with no active constraint.

(As in example below)

CREATE TABLE T1(
    T1_Id INT PRIMARY KEY CLUSTERED  NOT NULL,
    Filler CHAR(4000) NULL,
) 

INSERT INTO T1 VALUES (1, '');

CREATE TABLE T2(
    T2_Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    T1_Id INT NOT NULL CONSTRAINT FK REFERENCES T1 (T1_Id), 
    Filler CHAR(4000) NULL,
)

ALTER TABLE T1 ADD CONSTRAINT
    UQ_T1 UNIQUE NONCLUSTERED(T1_Id) 

/*Execution Plan uses clustered index*/ 
INSERT INTO T2 VALUES (1,1) 

ALTER TABLE T2  WITH CHECK ADD  CONSTRAINT FK2 FOREIGN KEY(T1_Id)
REFERENCES T1 (T1_Id)    

ALTER TABLE T2 DROP CONSTRAINT FK

/*Now Execution Plan now uses non clustered index*/    
INSERT INTO T2 VALUES (1,1)    

DROP TABLE  T2, T1;

As an aside for the interim period whilst both constraints exist any inserts end up being validated against both indexes.

StackExchange Database Administrators Q#234086, answer score: 18