composite primary key with foreign key

Question

I have a database 'STUDENT'. There are 4 table 'INFO','STREAM','GRADE' & 'MARKS'.

INFO has 3 columns ID, NAME, STR_NO.
STREAM has 2 columns STR_NO, STR_NAME.
GRADE has 2 columns PERCENT, GRADE.
MARKS has 3 columns ID, STR_NO, TOTAL_MARK, FULL_MARK

I have declared a composite primary key on info table i.e

PRIMARY KEY(ID, STR_NO)

But when I declare a foreign key on STREAM i.e

FOREIGN KEY(STR_NO) REFERENCES INFO(ID,STR_NO)

It gives error

Why and how to solve this

A foreign key must reference **all** columns of the primary key. — , Nov 27 '15 at 19:03
Which means you have to have the same two columns on the Stream table to use as foreign key (ID and STR_NO) — Jorge Campos, Nov 27 '15 at 19:12

score 0 · Accepted Answer · answered Nov 27 '15 at 19:46

If I understand what your tables are representing, it looks like you have the foreign key relationship backwards. It looks like STR_NO should be the primary key on STREAM, and that there should be a foreign key constraint on INFO requiring that INFO.STR_NO have a matching value in STREAM.STR_NO - or, in code

ALTER TABLE INFO
  ADD CONSTRAINT INFO_FK1
    FOREIGN KEY (STR_NO) REFERENCES STREAM(STR_NO);

Best of luck.

score 0 · Answer 2 · edited May 23 '17 at 11:59

0

Do this:

ALTER TABLE STREAM ADD(ID NUMBER)

ALTER TABLE INFO
  ADD CONSTRAINT INFO_FK1
    FOREIGN KEY (ID, STR_NO) REFERENCES STREAM(ID, STR_NO);

Since Table Info has a composite primary key ID, STR_NO, then any reference to it must also include both columns.

For more detail, see here.

edited May 23 '17 at 11:59

Community

1
1

answered Nov 28 '15 at 08:23

Muhammad Muazzam

2,810
6
33
62

composite primary key with foreign key

2 Answers2