Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Unanswered: no matching unique or primary key

    Trying to create a table with a Foreign Key and getting the above error.

    The first table creates fine:
    Code:
    CREATE TABLE ORDERS 
     (
        ORDERS_NUM         NUMBER       NOT NULL,
        CUST_NUM           NUMBER       NOT NULL,
        SHIP_STREET        VARCHAR(20)  NOT NULL,
        SHIP_CITY          VARCHAR(20)  NOT NULL,
        SHIP_STATE         VARCHAR(2)   NOT NULL,
        SHIP_ZIP           VARCHAR(5)   NOT NULL,
        SHIP_METHOD        VARCHAR(10)          ,
        ORDER_DATE         DATE         NOT NULL,
        SHIP_DATE          DATE                 ,
        PRIMARY KEY   (ORDERS_NUM, CUST_NUM),
        FOREIGN KEY   (CUST_NUM) REFERENCES CUSTOMER(CUST_NUM)
     );
    The second table is where I get the error:
    Code:
    CREATE TABLE ORDERDETAIL
    (
        DETAIL_NUM         NUMBER       NOT NULL,
        ORDERS_NUM         NUMBER       NOT NULL,
        PROD_NUM           NUMBER       NOT NULL,
        PROD_PRICE         DEC(10,2)            ,
        PROD_SHIP_COST     DEC(10,2)            ,
        QTY_PURCH          DEC(10,2)    NOT NULL,
        TOT_ORDER_WGHT     DEC(10,2)            ,
        ORDER_STAT	       DEC(10,2)    NOT NULL,
        TOT_PROD_COST      DEC(10,2)            ,
        TAX_RATE_ST        DEC(10,2)            ,
        ORDER_TAX          DEC(10,2)            ,
        TOT_SHIP_COST      DEC(10,2)            ,
        TOT_ORD_COST       DEC(10,2)            ,
        PRIMARY KEY   (DETAIL_NUM),
        FOREIGN KEY   (ORDERS_NUM) REFERENCES ORDERS(ORDERS_NUM),
        FOREIGN KEY   (PROD_NUM) REFERENCES PRODUCT(PROD_NUM)
    );
    Error is on this line:
    Code:
        FOREIGN KEY   (ORDERS_NUM) REFERENCES ORDERS(ORDERS_NUM),
                                                     *
    ERROR at line 17:
    ORA-02270: no matching unique or primary key for this column-list
    New to SQL, Can someone please assist? I am using Oracle SQL Plus.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    will ORDERS.ORDER_NUM be UNIQUE?
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    You need to create primary (or unique) key on ORDERS(ORDERS_NUM).
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Apr 2012
    Posts
    3
    Quote Originally Posted by LKBrwn_DBA View Post
    You need to create primary (or unique) key on ORDERS(ORDERS_NUM).
    I have a primary ORDERS_NUM) ON ORDERS. Maybe it is because I specified two primary keys on ORDERS?

  5. #5
    Join Date
    Apr 2012
    Posts
    3
    Quote Originally Posted by southwesternstar View Post
    I have a primary ORDERS_NUM) ON ORDERS. Maybe it is because I specified two primary keys on ORDERS?
    If I take out one of the primary keys, it works :-) Not sure why I cant have 2 but I will figure that out next.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by southwesternstar View Post
    I have a primary ORDERS_NUM) ON ORDERS
    No.

    You have a primary key on (ORDERS_NUM, CUST_NUM) which is something kompletely different.

    A foreign key must reference all columns of a primary key.

Posting Permissions

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