Hi guys,

I am having a bit of problem with my SQL coding. Just to let you know I am an amature at this (just started learning SQL 3 days ago) so please tell me all the mistakes I have in order for me to learn.

I have created a few tables and enforced referential integrity among them. With two of the related tables, I am getting a "ORA-02270: no matching unique or primary key for this column-list" error.

The two tables I am having problem with are the BED and PATIENT table. The following is what I have done so far...

CREATE TABLE BED
(BED_NUM NUMBER,
ROOM_NUM NUMBER,
WARD_ID VARCHAR2(8) CONSTRAINT fk_bedward REFERENCES WARD(WARD_ID),
NUM_BEDS NUMBER CONSTRAINT fk_bed_numbeds REFERENCES BEDRATE(NUM_BEDS),
CONSTRAINT pk_bed PRIMARY KEY(BED_NUM, ROOM_NUM, WARD_ID));

CREATE TABLE PATIENT
(PAT_ID VARCHAR2(8) cONSTRAINT pk_patient PRIMARY KEY,
LNAME VARCHAR2(15),
FNAME VARCHAR2(15),
ADDRESS_1 VARCHAR2(30),
ADDRESS_2 VARCHAR2(30),
CITY VARCHAR2(15),
POSTCODE NUMBER,
BED_NUM NUMBER,
ROOM_NUM NUMBER,
CONSTRAINT fk_pat_bedroom FOREIGN KEY(ROOM_NUM, BED_NUM) REFERENCES BED(ROOM_NUM, BED_NUM));

I've also tried doing it this way

CREATE TABLE PATIENT
(PAT_ID VARCHAR2(8) cONSTRAINT pk_patient PRIMARY KEY,
LNAME VARCHAR2(15),
FNAME VARCHAR2(15),
ADDRESS_1 VARCHAR2(30),
ADDRESS_2 VARCHAR2(30),
CITY VARCHAR2(15),
POSTCODE NUMBER,
BED_NUM NUMBER CONSTRAINT fk_patbed REFERENCES BED(BED_NUM),
ROOM_NUM NUMBER CONSTRAINT fk_patbedroom REFERENCES BED(ROOM_NUM));

And the same error message appears.

I cannot see what could be wrong with my coding, and I've been stuck here for hours trying to sort this out. I've also tried using ALTER to alter the PATIENT table and reference to BED table from there, but still same error message still appears.

Could someone please enlighten me?

When enforcing referential integritiy, columns that make foreign key MUST match those that make primary key.

For example:

Code:

CREATETABLE

See? Table "BED" has a primary key that consists of two columns: bed_num and room_num.

Therefore, each and every foreign key that references "BED" table MUST have such a combination. Patient has two columns that make its foreign key to the "BED" table: bed_num_example and room_num_example (those columns don't have to have the same name as those in primary key; that's why I put an "example" suffix).

Hi LittleFoot,

Thank you for the quick reply. I am still a bit lost.

When you are saying that foreign key must match those that make primary key, the tables i have now, they already match aren't they?

As with my BED table, i have bed_num and room_num as my primary keys, and in PATIENT table, i have bed_num and room_num as my foreign keys.

Lost and confused.

No, you don't ... Your code is:

CREATE TABLE BED
(...
CONSTRAINT pk_bed PRIMARY KEY
(BED_NUM, ROOM_NUM, WARD_ID)
);

CREATE TABLE PATIENT
(...
CONSTRAINT fk_pat_bedroom FOREIGN KEY
(ROOM_NUM, BED_NUM)
REFERENCES BED (ROOM_NUM, BED_NUM)
);

See the column WARD_ID contained in BED's primary key? You should reference it from the PATIENT table as well. But, as you don't have WARD_ID column in PATIENT table, you can't enlist it in foreign key statement (obviously).

Therefore:

• alter PATIENT table and add WARD_ID column so that you could include that column into foreign key statement, or
• alter BED table and remove WARD_ID from primary key

Hi LittleFoot,

Thank you so much for your help, it worked flawlessly! My hat off to you. Not only it solved that referential integrity problem, but also solved many other similar problems too.

Thank again LittleFoot! Cheers.

You're welcome
Do enjoy in Oracle!