CODEWITHSUNDEEP
sqldatabaseforeign-keysentity-relationship
Oscar
Oscar

Reputation: 487

How to map multiple foreign key from ER model to relational model?

I have an issue mapping an ER model into relational model. I try to simplify my case. Supposing to have 3 entities, A, B and C, and 2 relations, R1 and R2

[A] ---(1,n) <<R1>>(1,1) === [[B]] === (1,n)<<R2>> (1,1) ==== [[C]]

A has a key, ak and other attributes. B is weak and is identified by ak and bk (this a wek/partial key) Up to now .. all ok for mapping, I will have 2 relations

What for C? I think that C has ck like partial key, but how map the new relation?

C (ck, bk, ak, c1, ..., cn) with

or something else like e.g.:

Thinking on final SQL doesn't help much, since I think that multiple foreign_keys aren't permitted (eg. FOREIGN KEY (bk,ak) REFERENCES B(bk, ak)

I searched on many books but haven't found a similar situation. I'm really confused at this time... I hope you may help me :-) Thanks in advance

Upvotes: 1

Views: 784

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

The PRIMARY KEY of table B is (bk, ak) so this is what you should be referencing from C for a 1:n relationship:

(ck, bk, ak) as PRIMARY KEY,
(bk, ak) as FOREIGN KEY referencing B(bk, ak)

This is allowed in SQL:

CREATE TABLE C
( ck, bk, ak, ...
, PRIMARY KEY (ck, bk, ak)
, FOREIGN KEY (bk, ak) 
    REFERENCES B (bk, ak)
)

Upvotes: 1

Related Questions