Homework help: creating composite primary keys from foreign keys

[RIGorous1]

hello all,

I have a table called room that is defined as:

create table room (
room_no number(3) constraint room_pk primary key,
accommodation varchar2(20) constraint accom_fk references accommodation not null,
extension varchar2(6) constraint ext_null not null);

this forms a composite primary key in the table bed:

create table bed (
bed_no number(8),
room_no number(3) constraint room_bed_fk references room,
constraint bed_room_pk unique( primary key (room_no, bed_no));

Now I want to use those foreign keys to form another primary key that has (room_no, bed_no, date, patient_no) in another table.

the error comes here:
SQL> create table Patient_Bed_History (
2 bed_date date,
3 patient_no number(8) constraint pbh_patno_fk references patient,
4 bed_no number(8) constraint pbh_bed_fk references BED,
5 room_no number(3) constraint pbh_room_fk references room,
6 constraint pbh_pk primary key (patient_no, room_no, bed_no, bed_date));
bed_no number(8) constraint pbh_bed_fk references BED,
*
ERROR at line 4:
ORA-02256: number of referencing columns must match referenced columns

how do I fix this?

Thanks for your help

 

[RIGorous1]

no one wants to give it a try?

 

[JSFLY]

obviously the number of referencing columns don't match the referenced columns.

 

[narzy]

Does any of this help?

http://www.techonthenet.com/oracle/errors/ora02256.php

Make sure that the referencing columns match the referenced columns.

try drawing the relationships out using something like a pencil and paper, excel, or my favorite, visio....or however it's spelled.