Results 1 to 2 of 2

Thread: no matching unique or primary key

  1. #1
    Join Date
    Apr 2012
    Posts
    1

    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
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    That is because the primary key on Orders table is ORDERS_NUM, CUST_NUM not just ORDERS_NUM.

Posting Permissions

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