Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Unanswered: problem with foreign keys

    Hiya, I'm having a prob I'd really appreciate any help. I get the message 'ORA-02273: this unique/primary key is referenced by some foreign keys' every time i try these foreign keys. I read another post on something similar but still need some help.

    For example I have 2 tables:

    CREATE TABLE EQUIPMENT
    (serialno varchar2(7) not null,
    equip varchar2(30),
    primary key (serialno) );

    CREATE TABLE ROOMBOOKING
    (bookingdate date not null,
    sess char(1) not null,
    roomno number(3) not null,
    misc varchar2(30),
    primary key (bookingdate, sess, roomno) ); <<SO COMPOSITE KEY HERE

    Now I need to make foreign keys in another table to some of these columns but I keep getting there error. For example I've got a table called EQUIPMENTBOOKING that contains
    sess, bookingdate, roomno, serialno

    but I cant get the keys working.

    ALTER TABLE EQUIPMENTBOOKING
    add constraint fkey_room
    foreign key (bookingdate, roomno, sess) references roombooking (bookingdate, roomno, sess) ;


    tried one at a time

    ALTER TABLE EQUIPMENTBOOKING
    add constraint fkey_room
    foreign key (bookingdate) references roombooking (bookingdate) ;

    still get the error. And still havent put in the other table one yet, its roombooking thats causing the problem. Can one help?

    thanks alot

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try specifying the FK columns in the same order as the PK columns

    in fact you should be able simply to reference the table, and it will figure out the PK

    ALTER TABLE EQUIPMENTBOOKING
    add constraint fkey_room
    foreign key (bookingdate, sess, roomno) references roombooking
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    2
    Aaah, thanks a lot. That fixed it up. Working now, thankyou.

Posting Permissions

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