CODEWITHSUNDEEP
sql-serverdatabase-design
user5014677
user5014677

Reputation: 694

ForeignKey column comes from a lot of tables

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?

Upvotes: 0

Views: 43

Answers (1)

Raven-Blue Dragon
Raven-Blue Dragon

Reputation: 614

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?

Upvotes: 1

Related Questions