Is it possible to create a foreign key constraint references to more than one primary keys from different tables? If so, how would the sript look like.
Thanks,
u
Printable View
Is it possible to create a foreign key constraint references to more than one primary keys from different tables? If so, how would the sript look like.
Thanks,
u
Dont really understand what do you mean.
But one FK references to one PK, you cannot reference to multiple PKs in different tables unless you create one FK per PK
FK is also called Parent-Child relationship.
Each child must have one and *only* one parent.
So no, the foreign key cannot map to more than one Primary key.
- Rajeev
Thanks, that's what I think. I got a database from a client who has 2 identical table structures (not in Oracle database though). I just wondered how was the relationships set up in that database and now transfering into oracle. I think one way I can do is to create another unique field then combine these two tables together into one. Thanks for your help.
-U
Can you explain the problem in more detail?
- Chris
Logically a FK is mapped to PK of another Table. This is how ERD works.
But RDBMSs allow a FK to be mapped with multiple PKs of 2 or more Tables and all the constraints are validated whenever data is inserted/updated on FK column.
Actually, you just confused me even more.
Why in the world would you want a single FK to reference multiple PKs?
If we have T1 with C1 as the PK and T2 with C1 as the PK, we could create an FK in T2 that references T1.C1 AND T2.C1, thereby 'intersecting' the columns. What benefit would this provide. Are we saying that the value is only valid if it exists in both parents (Case 1)? Or is it valid if it exists in either? (Case 2)
Ah, here is the crux of the question - what is the functionality we are hoping to implement by trying to map a single FK to 2 different PKs? More importantly, what is the relationship between these parents that they share PKs?
When 2 tables share a common PK, this means we usually have a 1:1 or 1:0,1 relationship between the two tables. In either case, one of the tables is the parent and the other is the child. The child must exist in the parent, but the parent does not need to exist in the child.
Therefore, for Case 1: Have the FK point to the child table. For Case2, have the FK point to the parent.
Another possibility is that both of these 'parent' tables are actually children of the same parent. For example, we have a Contract_T table with a PK of contract_Id. We then have sub-type relationships to 2 children: FixedPriceContract_T and TAndMContract_T, which both have Contract_ID as their PKs.
Let's assume that these are the 2 'parent' tables that you want your FK to point to. Actually, let's assume that there are multiple types of contracts and you are only interested in 2. First, one more fact about sub-type relationships - the way you know which type of child you have is through the use of a type column (usually). In Contract_T, their is a ContractType_ID field that tells us which child table this contract exists in. So back to the problem at hand: we have come up with this new table and we only want to allow T and M or fixed price contracts to use this table. In such a case, what you are modelling, at the most basic level, is a relationship to the parent - the Contract. You are saying that this is a table that contains information about contracts. Therefore, you should point the FK at the Contract_T table. If you want to only allow specific types of contracts, then add a trigger that checks the ContractType_ID column when a record is inserted or updated.
Basically, it is my opinion that this problem not only *can* be solved through other means, but *should* be solved through other means. Pointing a single FK at multiple PKs is not the correct answer.
Now, the other possibility is that you want to combine dis-similar PKs. As in, we have T1.C1 and T2.C2 and we want T3.C1, T3.C2 to reference the combination of the PKs from T1 and T2. This would appear to be a non-sensical case to me. What would 'linking' these 2 separate FK/PK relationships buy you? Nothing that I can think of. Just make 2 separate FKs.
This is why I asked for more detail, so I would have more of a clue what you were truly trying to do so I could save myself some typing, but oh well ;)
- Chris