Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Red face Unanswered: no matching unique or primary key for this column-list

    Hi

    This is my first time here, but I noticed that someone else had a similar problem and I would really appreciate the help... I am getting the above error for the last table I am trying to create. All the other tables created perfectly.. but then when I got to the last one. I got this error...

    This is the table that is not working:

    CREATE TABLE SUPPLIES
    ( SUPPLIESID NUMBER(4) NOT NULL,
    SERVICE VARCHAR2(100),
    REFNUMBER VARCHAR2(6),
    SUPPLIERSID NUMBER(4) NOT NULL,
    EVENTID NUMBER(4) NOT NULL,
    CONSTRAINT PKSUPPLIES PRIMARY KEY (SUPPLIESID, EVENTID, SUPPLIERSID),
    CONSTRAINT FKSUPPLIES FOREIGN KEY (EVENTID)
    REFERENCES EVENT (EVENTID) ON DELETE CASCADE,
    CONSTRAINT FK2SUPPLIES FOREIGN KEY (SUPPLIERSID)
    REFERENCES SUPPLIERS (SUPPLIERSID) ON DELETE CASCADE);

    and this is the table that it is referencing that was created before without any problems...

    CREATE TABLE SUPPLIERS
    ( SUPPLIERSID NUMBER(4) NOT NULL,
    SUPPLIERSNAME VARCHAR2(50),
    SUPPLIERSADDRESS VARCHAR2(50),
    SUPPLIERSCONTACT NUMBER,
    SUPPLIERSEMAIL VARCHAR2(50),
    SUPPLIERTYPEID NUMBER(4) NOT NULL,
    CONSTRAINT PKSUPPLIERS PRIMARY KEY (SUPPLIERSID, SUPPLIERTYPEID),
    CONSTRAINT FKSUPPLIERS FOREIGN KEY (SUPPLIERTYPEID)
    REFERENCES SUPPLIERTYPE (SUPPLIERTYPEID) ON DELETE CASCADE);

    Thanks in advance whoever can help, I don't know if I've been looking at this too long, why I am not seeing what went wrong...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the primary key of suppliers is (suppliersid, suppliertypeid), and suppliers has no other unique key

    therefore the fk2supplies foreign key, which is just one column, has no matching primary or unique key in suppliers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Foreign key (FK2SUPPLIES) must reference a primary key or other unique key in the parent table, which is not the case in your example. You are trying to reference a part of the primary key.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Aug 2009
    Posts
    2
    Hi r937

    Thanks for the response, however, when I try to reference the pk in Supplier as SuppliersID and SupplierTypeID I am still getting an error, can you help me out please... I really don't know how to fix it...

    Thanks.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >when I try to reference the pk in Supplier as SuppliersID and SupplierTypeID I am still getting an error
    Yes, you do but you decided we did not need to see it. :-(


    Without additional changes table SUPPLIES does not contain column SupplierTypeID

    With camel backed Capitalization, I suspect this SQL originates on different RDBMS.

    In the future use CUT & PASTE of the whole session so we can see exactly what you did & how Oracle responded.
    Last edited by anacedent; 08-13-09 at 17:18.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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