1

I am interested if it is possible to have flexible foreign keys where the parent table is defined by a field in the table - I have search extensively for this but am not sure if I have found the correct terms to find the answer, please help! Hopefully the below example explains the problem well enough. I'm working in SQL Server 2016 for reference.

Thanks!

Example: I have 3 tables (let's call them A, B and C) on the same level of hierarchy. I then have a fourth table (D) that contains data that could be related to any of the first 3 tables. Rather than having 3 versions of table D, each with a foreign key relationship to either of A, B or C is it possible to have a foreign key in D where one field defines the target table (either A, B or C) and then a second field that contains the id value from the relevant table.

So table D would look like this:

id    ForeignTableName ForeignTableID DataPoint1
1     A                3              xxx
2     C                6              xxx
3     B                2              xxx
4     B                5              xxx
5     A                10             xxx
tadman
  • 208,517
  • 23
  • 234
  • 262
Tom Hull
  • 13
  • 2
  • SQL Server doesn't support creating such constraint. – Ilyes Dec 15 '17 at 19:21
  • I'd be curious about the SQL you plan on writing. Is `FROM a inner join d on a.id = d.ForeignTableID and d.ForeignTableName = 'A'` really that much better than `FROM a inner join a.id = d.aid` and then ensuring that one and only one field is populated? – Conrad Frix Dec 15 '17 at 20:57

1 Answers1

1

It is not possible to do this with a Foreign Key Constraint. This kind of RI can be enforced either with a CHECK CONSTRAINT that calls a function, or with a Trigger.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52