Foreign Key Duplication?

Probably a simple answer here, I hope, but seems like a difficult question to put into words.

If you have a Foreign Key that essentially appears twice in a table, coming from two 开发者_运维百科Composite Keys does that FK then have to be defined twice (exist as two separate attributes)?

Here are some simple models to visualize what I am asking. Ex. 1 shows the FK of Table1Id as a single attribute. Ex. 2 shows the FK of Table1Id (identified as Table1Id_FKTable2 & Table1Id_FKTable3) as two different attributes.

Depending on what you want to accomplish are both of these models valid?

Usually, the table1ID_FKTable2 is better called a "role" that Table1 plays with respect to Table4.

And similarly, table1ID_FKTable3 is a different "role" that Table1 plays with respect to Table4.

The idea of role is pervasive. Two employees belong to the same company, but may have different roles, one as member of the board of directors, the other as part-time, hourly.

I am trying to understand your question, but if the primary key of Table2 is a composite of Table1Id and Table2Id then you need to create a foreign key from Table4 using both fields. Same thing for Table3.

ALTER TABLE Table4 ADD constraint fk_tab2 foreign key references Table2(Table1Id, Table2Id)
ALTER TABLE Table4 ADD constraint fk_tab3 foreign key references Table3(Table1Id, Table3Id)

Your drawings do not "visualise what you are asking", because your drawings do not appear to contain any composite keys at all.

But if your question is (something like) "Can one single attribute in table4 be declared to be a foreign key BOTH into table2 and table3 ?", then the answer is yes.

It would be unlikely if it worked the way you'd want it to if you use ID fields all over the place, but neither the relational model nor even the SQL standard prevent it.