Best Practices to have Mutually Exclusive Foreign Keys for table in Database in SQL Server

Question

Here I am looking for the best solution with pros and cons for the below-mentioned problem:-

Entity1 (E1(pk)) with other different columns
Entity2 (E2(pk)) with other different columns
Entity3 (E3(pk)) with other different columns

I need to create the relationship between Entity1 and Entity3 (E1 should work as FK in Entity3, also want to create a relationship between Entity2 and Entity3 (E2 should work as FK in Entity3)

But two foreign keys E1 and E2 must be mutually exclusive - so every record in the Entity3 table can either be connected to Entity1 or Entity2, but not to both on the same time.

Solution 1:- In Entity3, I will have two columns for E1 and E2, but in this, either E1 or E2 will always null.

Solution 2:- I can duplicate Entity3 tables with different FK in each table, but in this, I have to repeat the schema for Entity3.

Solution 3:- I will have only one column in the Entity3 table that will not have FK on that column. Also, I will introduce one column for the type that will signify the Entity1 or Entity2 table entry, but in this, I will not be able to follow data integrity.

Please let me know the best answer to this situation or any other approach that will overcome my above-mentioned cons.

Answer 1

Between the solutions you have suggested in the question, I would absolutely go with Solution 1# - adding a different column for each foreign key - but also add a check constraint to the Entity3 table, ensuring at least one of these columns is null for each row - something like this:

ALTER TABLE Entity3
    ADD CONSTRAINT ck_MutuallyExclusiveFK 
    CHECK (FK1 IS NULL OR FK2 IS NULL)
GO

If you want to make sure at least one of the columns is not null, you can do this:

ALTER TABLE Entity3
    ADD CONSTRAINT ck_MutuallyExclusiveFK 
    CHECK ((FK1 IS NULL OR FK2 IS NULL) AND (FK1 IS NOT NULL OR FK2 IS NOT NULL))
GO

Solution #3 is by far the worst. Not only you lose data integrity, you also add a complication with the type column.

Solution #2 is also bad, because having multiple tables for the same entity is a bad design - not just because of the maintenance problem of taking care of duplicates, but also because the fact that it's the same entity means you will probably need to treat these duplicates as the same table throughout your database and application code, and that can lead to a lot of problems and jumping through hoops workarounds.