CODEWITHSUNDEEP
sqlsql-serverforeign-keys
lenden
lenden

Reputation: 830

Is it possible to add more than one foreign key to one field in table

Can I realise something like it? For example if some other field value is 'a', foreign key should be to table a, if value is 'b', it should be key to table b? I understand that it is not normal and doesn't match idea of foreign keys, but is it possible to do something to make this scheme real in fact?

Update :

I have a schema like this:

There are products. They can contain different additional fields, for example standart product can content no additional fields, but "picture" can contain fields like "author" and so on. Really "standart product" and "picture" can differ ideologically, not only because of additional fields.

There is table like "Order", that should contain link to product and other data. But when I put field like "product id", it should be data, is it standart product id, or picture id.

There can be design like table of additional fields, but there will be another problem. I can make table(id, additional_field_name) and table for mapping (id, add_field_id, product_id, field_value), but field_value can be number and can be string, so this idea is not good I think.

Upvotes: 2

Views: 973

Answers (3)

Chains
Chains

Reputation: 13157

Per your comment & revised question:

Products

ID
Description
AdditionalFields_ID

Additional Fields

AdditionalFields_ID
Author

Then use a LEFT JOIN, so that your query only pulls additional fields when they exist (like when the product type is a picture).

SELECT *
FROM Products P LEFT JOIN AdditionalFields A ON P.AdditionalFields_ID = A.AdditionalFields_ID

If you want a better answer than that, you're going to have to provide some real examples of your table structure & data in your question.

Upvotes: 0

Ben
Ben

Reputation: 309

For foreign key constraints, you'd need to use separate nullable columns, one for each potential foreign key relationship.

You could hack this together with check constraints, but you're likely better off with separate columns, both from a performance standpoint and from a consistency standpoint.

Upvotes: 0

Ben Thul
Ben Thul

Reputation: 32737

As stated, no you can't do that. You'd need to have two separate columns. Besides, interpreting the value of a column based on the value of another column doesn't feel right to me.

Upvotes: 2

Related Questions