Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    3

    Question Unanswered: No matching unique or primary key error, help :o

    Hello one and all, 1st time poster, so I'd first like to say that I love the forum quite alot, although alot of the things go over my head, im still trying to learn

    Secondly, I was wondering if anyone could help me in the following problem:

    ORA-02270: no matching unique or primary key for this column-list" error appears when i try to create the following table
    Code:
    CREATE TABLE PARTY
    ("C-ID" CHAR(5) NOT NULL,
    BOOKING# NUMBER NOT NULL,
    PRIMARY KEY ("C-ID", BOOKING#),
    FOERIGN KEY ("C-ID") REFERENCES CUSTOMER ("C-ID"),
    FOREIGN KEY ("BOOKING#) REFERENCES BOOKING (BOOKING#))
    My other two tables are:
    Customer Table:
    Code:
    CREATE TABLE CUSTOMER
    ("C-ID" CHAR(5) NOT NULL,
    C_NAME CHAR(25),
    C_DOB DATE,
    C_ADD CHAR(50),
    TEL_DAY NUMBER,
    TEL_EVE NUMBER,
    "C-PROF" BLOB,
    PRIMARY KEY ("C-ID"))
    And Booking Table:
    Code:
    CREATE TABLE BOOKING
    (COURSE# NUMBER NOT NULL,
    BOOKING# NUMBER NOT NULL,
    BOOKING_DATE DATE,
    PAYMENT CHAR(1),
    PRIMARY KEY (COURSE#, BOOKING#),
    FOREIGN KEY (COURSE#) REFERENCES COURSE (COURSE#))
    Now, having read some of the previous posts, i can only come up with the fact that i must use both primary keys from booking table in Party table. How ever that also didn't work.

    Any help will be hugely appreciated, Thank you

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hello,
    ORA-02270: no matching unique or primary key for this column-list
    Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.
    Action: Find the correct column names using the ALL_CONS_COLUMNS catalog view
    The error message is quite clear:
    Table PARTY has foreign key
    Code:
    FOREIGN KEY ("BOOKING#) REFERENCES BOOKING (BOOKING#)
    , but table BOOKING has primary key
    Code:
    PRIMARY KEY (COURSE#, BOOKING#)
    You cannot reference to BOOKING.BOOKING#, as it is NOT unique; it is unique only with combination with COURSE#.

    Check your design, not sure, what are you trying to achieve.
    Also try to avoid special characters (eg. "-#) in column names; you are just going to have troubles with it.

  3. #3
    Join Date
    Dec 2007
    Posts
    3
    Ah yes, I thought it could be the design, Thank you very much, you have re-assured me that the changes im making are correct

    The design had some flaws in it, obviously :P

    Thanks alot for the fast reply, really appreciated

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Just a side note: if you want to save yourself a lot of trouble, do not use special characters like # or - in column (or table) names.

    Ah and another thing: Are you sure you want a CHAR(5) field?

    This will automatically be (right) padded to 5 characters.
    So when you insert 'A' it will be stored as 'A ' in the database, and a condition like WHERE "C-ID" = 'A' will not match any rows.

    I 90% of the cases people want VARCHAR2 when they write CHAR

  5. #5
    Join Date
    Dec 2007
    Posts
    3
    Oh i see, well the char (5) is based on the fact the the C-ID will always be made up of 5 characters, but i understand why VARCHAR maybe more appropriate, Thank you.

    I dont quite understand how special characters, such as #, may cause trouble though, as my lecturer seems to use them all the time, LoL and in alot of the example tables she has given us, number is almost always shortened to #.

    But i will change it to number, thank you very much

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by MoobeH
    Oh i see, well the char (5) is based on the fact the the C-ID will always be made up of 5 characters
    If it's always 5 characters, then CHAR(5) is the right choice (those other 10% )
    I dont quite understand how special characters, such as #, may cause trouble though
    Well at least the dash (-) will require you to quote all column names (which also makes them case-sensitive: "C-ID" is something different than "c-id"), and there are tools out there which do have a hard time supporting this.
    It seems that Oracle indeed happily accepts the # without problems which I wasn't aware of. So you proved me wrong there

Posting Permissions

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