Ambiguous foreign keys 1

[Luzian]

Apparently it is totally legal to do this:

CREATE TABLE [Apples]
(
	[id] int NOT NULL IDENTITY PRIMARY KEY,
	[yumminess] int NOT NULL
)
CREATE TABLE [Oranges]
(
	[id] int NOT NULL IDENTITY PRIMARY KEY,
	[yumminess] int NOT NULL
)
CREATE TABLE [EatenFruits_LOG]
(
	[fruit] int NOT NULL,
	[when] datetime NOT NULL DEFAULT GetDate()
)

[red]ALTER TABLE [EatenFruits_LOG]
ADD FOREIGN KEY ([fruit])
REFERENCES [Apples]([id])


ALTER TABLE [EatenFruits_LOG]
ADD FOREIGN KEY ([fruit])
REFERENCES [Oranges]([id])[/red]

In red, you see that I have two tables whose primary key is linked to a single foreign key. Why is this allowable? Why would anyone want to do this? Does this cause a problem? If I inserted a row into the [tt][EatenFruits_LOG][/tt] table, how the hell would I know later whether the data was meant to be the id of an apple or an orange?

 

[r937]

it's possible because it stipulates that any new value of [EatenFruits_LOG].[fruit] must already exist in both referenced tables

the examples you chose are unfortunate, because we all know that something cannot be both an apple and an orange

however, i'm sure it's possible to come up with a better example

r937.com | rudy.ca

 

[Luzian]

I understand. Why would you want to enforce the same data to exist in 2 locations?

 

[r937]

it does not exist in two locations -- it's in a separate, third table

it does have two relationships, however

r937.com | rudy.ca

 

[Luzian]

Okay, it's not in another location which is jargon that can be interpreted as another server. Its proximity has past the set boundaries of an entity I'd like to call a table, into another conceptual zone I'd like to call a table.

Why would you have the same data in two different tables in this scenario where we have two relationships refering from the primary keys of those two tables to a particular column of a single table?

 

[r937]

it isn't the same data in two different tables

okay, i'm going to make up another example that is hopefully a little more realistic than your apples and oranges

suppose table 1 is a registry of programmers, so that every row identifies a person (the PK) with attributes that identify their programming skills (the PK is of course also a FK to the persons table)

and suppose table 2 is a registry of designers, so that every row identifies a person (the PK) with attributes that identify their designing skills (the PK is of course also a FK to the persons table)

now suppose that the third table is a table of candidates that would be suitable for jobs requiring both programming and design skills -- this table would have FKs to the other two tables, which means that you cannot add a row to this table unless the FK you provide is present in both tables 1 and 2



r937.com | rudy.ca

 

[Luzian]

I got it now. Thanks