CODEWITHSUNDEEP
sql
Nirmith Akash
Nirmith Akash

Reputation: 23

Foreign key references mutiple columns in the database

When I get my EER converted to the relational schema I got this table which has a composite primary key like following,

CREATE TABLE branch
(
    branch_number varchar(20),
    b_code varchar(20),
    name varchar(20),
    address varchar(20),

    --CONSTRAINTS
    CONSTRAINT pk_branch PRIMARY KEY(branch_number,b_code),
    CONSTRAINT fk_branch FOREIGN KEY(b_code) REFERENCES bank(b_code)

)

This above table is a weak entity and therefore it's having a key combination as a primary key. (due to the identifying relationship) Then this branch table is in a one-to-many relation with the following table 

CREATE TABLE account 
(
    account_no varchar(20),
    balance float,
    branch_number varchar(20),
    b_code varchar(20),
    a_code varchar(20),

    --CONSTRAINTS
    CONSTRAINT pk_account PRIMARY KEY(account_no),
    CONSTRAINT fk_account_1 FOREIGN KEY(b_code) REFERENCES branch(b_code),
    CONSTRAINT fk_account_2 FOREIGN KEY(branch_number) REFERENCES branch(branch_number),
    CONSTRAINT fk_account_3 FOREIGN KEY(a_code) REFERENCES account_type(a_code)
)

How do I make the relation between these two tables? How to implement the foreign key for the account table with branch when I have one column referencing to two other columns?

Upvotes: 0

Views: 42

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Your fk_account_1 will fail when you try to compile it because b_code is not a primary or unique key on table branch. Same problem with fk_account_2, because branch_number is not primary or unique on branch.

The primary key on branch is (branch_number,b_code) so your foreign key should be

CONSTRAINT FK_ACCOUNT_1
    FOREIGN KEY (BRANCH, B_CODE)
    REFERENCES BRANCH (BRANCH, B_CODE);

Upvotes: 1

Related Questions