0

So i have a table in which i have a column named parentKey. And this column has actually keys (which by definition are foreign keys) to MANY other tables (at least 4). And it seems strange to me to even create a column like this. I haven't yet seen a construction of a table that had this. Because you can't add a foreign key constraint since the column doesn't link to one single table. So i don't know is this is allowed to exist. I mean it's there it is created but i'm not sure if i should let it like this.

My idea is to create a column for each of the possible tables and name it correctly like : MyTable1Key, MyTable2Key and let them be foreign keys. But the problem with that is that if one of the foreign keys is assigned then the other ones will be null (And it will never be assigned so it will always stay null).

So do i have to let this parentKey column like it is or should i split it to different columns linked to tables by foreign keys and so have null values for some columns?

user5014677
  • 694
  • 6
  • 21
  • *"So do i have to let this parentKey column like it is..."* No. *"...should i split it to different columns linked to tables by foreign keys and so have null values for some columns?"* No. You almost certainly want something [along these lines](https://stackoverflow.com/a/17407959/562459). But there's not enough detail in your question to be sure. – Mike Sherrill 'Cat Recall' Jul 12 '17 at 11:43
  • @MikeSherrill'CatRecall' No, that other guy seems to reference one table each time. Like a column that reference a car type. CarType is inside one table. In my table the column reference other tables not a single column inside a single table. Like the columnA in TableA reference columnB in tableB but it can also be a reference to columnC in tableC or a reference to columnD in tableD but always only 1key to one column of another table. The key is a foreign key to always one table. And i have absolutly never seen something like that. Usually a column reference one table, not many. – user5014677 Jul 12 '17 at 12:11
  • @MikeSherrill'CatRecall' It looks strange to me to do something like that but i don't have enough experience so maybe this is a common thing to do. From what i know i would seperate this column and have one foreign key column for each table that this current column is referencing to. But that means that if i have one key in one of the columns the rest will be null. – user5014677 Jul 12 '17 at 12:15
  • Edit your question, and include code with actual table names. (Or something close to actual table names.) Include `create table` and `insert into` statements. – Mike Sherrill 'Cat Recall' Jul 12 '17 at 13:02
  • @MikeSherrill'CatRecall' But it's concerning the database design. There's no code – user5014677 Jul 12 '17 at 13:13
  • What is the problem that you're trying to solve? – Raven-Blue Dragon Jul 12 '17 at 14:09
  • @Raven-BlueDragon The problem is that i have never seen one table with one column that is a foreign key in 1 of 3 possible other tables instead of having 3 columns each being a foreign key for 1 other table. And i'm not sure if i should let it be like this. – user5014677 Jul 13 '17 at 07:20

1 Answers1

1

Unless you have a good reason, do not combine multiple foreign keys into a single column. As you've already noted it removes the referential integrity of your foreign key.

Either you will risk having a key which could belong to two tables or you have a master table somewhere that you should use as your foreign key reference. It is possible to have a primary key as a foreign key.

It sounds like you may be looking at the supertype-subtype pattern in which case this question might give you some good ideas. How do I apply subtypes into an SQL Server database?

Raven-Blue Dragon
  • 428
  • 6
  • 11
  • Oh yeah i see the answer is exactly the problem i'm facing and he suggests to do as i thought. Make a column per table so i can have foreign keys and a row will therefore have a null for some foreign keys. Thanks, helps a lot. – user5014677 Jul 17 '17 at 07:27