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

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.

There is no use for having identical foreign key constraints., that is on same columns and referencing same table and columns.

It's like having the same check 2 or more times.

There is no benefit to having redundant constraints that differ only by name. Similarly, there is no benefit to having redundant indexes that differ only by name. Both add overhead without value.

The SQL Server database engine does not stop you from doing so. Good constraint naming constraint naming conventions (e.g. FK_ReferencingTable_ReferencedTable) can help protect one against such mistakes.