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

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

Same reason you can create 50 indexes on the same column, add a second log file, set max server memory to 20MB... most people won't do these things, but there can be legitimate reasons to do them occasionally, so there's no benefit in creating overhead in the engine to add checks against things that are merely ill-advised.

Answer 5

Sounds like a blue-green thing.

When you begin to cutover from blue to green, you need to temporarily create extra copies of things.

What we want to do is temporarily create an extra foreign key CHECK WITH NOCHECK and ON UPDATE CASCADE ON DELETE SET NULL; what this does is it is a working foreign key but the existing rows aren't checked when the key is created.

Later after cleaning up all the rows that should match we would create the new foreign key without any command options (default is CHECK WITH CHECK which is what you typically want), and drop the temporary foreign key.

Notice that if you just dropped and recreated the foreign key, some garbage rows could slip by you.