Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    7

    Question Unanswered: Strange problem - no matching unique or primary key...

    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?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    When enforcing referential integritiy, columns that make foreign key MUST match those that make primary key.

    For example:
    Code:
    CREATETABLE
    Code:
     bed
    (bed_num NUMBER,
    room_num NUMBER, 
    ward_id VARCHAR2(8),
    num_beds NUMBER,
    CONSTRAINT pk_bed PRIMARY KEY(bed_num, room_num)
    );
    
    CREATETABLE 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_example NUMBER,
    room_num_example NUMBER,
    CONSTRAINT fk_patbed FOREIGN KEY(bed_num, room_num)
    REFERENCES bed (bed_num, room_num)
    );
    


    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).
    Last edited by Littlefoot; 05-17-04 at 06:01.

  3. #3
    Join Date
    May 2004
    Posts
    7
    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by pkipper
    Hi LittleFoot,
    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.
    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

  5. #5
    Join Date
    May 2004
    Posts
    7

    Thank you!

    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.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You're welcome
    Do enjoy in Oracle!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •