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?
Best Answer
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.