Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    13

    Unanswered: error create table - 2 fields as PK

    Hello

    I'm stuck with creating a table. The table I want to create is called backorder. I created a few other tables that are involved. The shipping table has 2 fields as a PK.

    CREATE TABLE inventory
    (invid NUMBER(5) CONSTRAINT inventory_invid_pk PRIMARY KEY,
    itemid NUMBER(5) CONSTRAINT inventory_itemid_fk REFERENCES item(itemid),
    itemsize CHAR(1),
    color VARCHAR2(10) CONSTRAINT inventory_color_fk REFERENCES color(color),
    curr_price NUMBER(5,2) CONSTRAINT inventory_curr_price_nn NOT NULL,
    qoh NUMBER(5) CONSTRAINT inventory_qoh_nn NOT NULL);

    CREATE TABLE shipping
    (shipid NUMBER(5) CONSTRAINT shipping_shipid_nn NOT NULL,
    invid NUMBER(5) CONSTRAINT shipping_invid_fk REFERENCES inventory(invid),
    CONSTRAINT shipping_shipid_invid_pk PRIMARY KEY (shipid, invid),
    date_expected DATE CONSTRAINT shipping_date_expected_nn NOT NULL,
    date_recieved DATE,
    quantity_recieved NUMBER(5));


    Now I'm confused how to create the backorder table. This is what I have thought so far. I don't think it's right, especially with the shipid and the invid. I know cuz I got an error saying " no matching unique or primary key for this column-list". How would I combine the shipid and invid field as a FK to reference to the shipping table???


    CREATE TABLE backorder
    (backorderid NUMBER(5) CONSTRAINT backorder_backorderid_pk PRIMARY KEY,
    shipid NUMBER(5) CONSTRAINT backorder_shipid_fk REFERENCES shipping(shipid),
    invid NUMBER(5) CONSTRAINT backorder_invid_fk REFERENCES shipping(invid),
    date_expected DATE CONSTRAINT backorder_date_expected_nn NOT NULL,
    quantity_expected NUMBER(5) CONSTRAINT backorder_quantity_expected_nn NOT NULL,
    date_recieved DATE,
    quantity_recieved NUMBER(5));

  2. #2
    Join Date
    Feb 2003
    Posts
    5

    Re: error create table - 2 fields as PK

    Hi MaxPlus,
    See my example below. This should help you solve you problem.

    create table z(a number, b number, constraint c primary key(a,b));
    create table y (d number, e number, constraint fk foreign key(d,e) references z (a,b))

    I have given z(a,b) for your clarity. As a, b form the pk of z you can simply say references z.

    Hope it helps.
    Rgds,
    Deepa.

  3. #3
    Join Date
    Jan 2002
    Location
    India
    Posts
    15
    Hi Maxplus,
    I have skipped some of the fields for my convenience, try this

    CREATE TABLE SHIPPING (
    SHIPID NUMBER NOT NULL,
    INVID NUMBER NOT NULL,
    QTY NUMBER,
    CONSTRAINT SHIINVPK
    PRIMARY KEY ( SHIPID, INVID ) ) ;

    ALTER TABLE SHIPPING ADD CONSTRAINT INVIDFK
    FOREIGN KEY (INVID)
    REFERENCES INVENTORY (INVID) ;


    CREATE TABLE BACKORDER (
    BACKORDERID NUMBER NOT NULL,
    SHIPID NUMBER,
    INVID NUMBER,
    QTY NUMBER,
    CONSTRAINT BKPK
    PRIMARY KEY ( BACKORDERID ) ) ;

    ALTER TABLE BACKORDER ADD CONSTRAINT SHIPIDINVFK
    FOREIGN KEY (SHIPID, INVID)
    REFERENCES SHIPPING (SHIPID, INVID) ;

Posting Permissions

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