Why can't I create this table?

[majorbroncosfan]

When I try and do the following, I get this error:

ORA-02270: no matching unique or primary key for this column-list


CREATE TABLE ship_line
( date_shipped DATE,
order_id NUMBER(6),
item_id CHAR(6),
location_id_from NUMBER(2),
qty_shipped NUMBER(5) DEFAULT 1,
method_shipped VARCHAR2(10),
tracking_id CHAR(15),
CONSTRAINT pk_ship_line PRIMARY KEY (date_shipped, order_id,
item_id, location_id_from),
CONSTRAINT fk_order_line FOREIGN KEY (order_id, item_id)
REFERENCES order_line (order_id, item_id));


CREATE TABLE item_location
( item_id CHAR(6),
location_id NUMBER(2),
qty_on_hand NUMBER(5) DEFAULT 1,
qty_on_order NUMBER(5) DEFAULT 1,
order_due_date DATE,
CONSTRAINT fk__item FOREIGN KEY (item_id)
REFERENCES items (item_id),
CONSTRAINT fk_ship_line FOREIGN KEY (item_id)
REFERENCES ship_line (item_id),
CONSTRAINT fk_ship_line_loc FOREIGN KEY (location_id)
REFERENCES ship_line (location_id_from),
CONSTRAINT pk_item_location PRIMARY KEY (item_id, location_id)
);


Does anybody have any idea why?

[skhanal]

Have you defined a primary key on order_line table ?

[majorbroncosfan]

Yes

[YuckFou]

you can't create FK with one column that will reference table with PK based on four columns (date_shipped, order_id, item_id, location_id_from)..
the FK will be not unique so you can't create this FK...

maybe you can create unique keys on ship_line, like this..

ALTER TABLE ship_line ADD CONSTRAINT uq_ship_line_item_id UNIQUE (item_id);
ALTER TABLE ship_line ADD CONSTRAINT uq_ship_line_location_id_from UNIQUE (location_id_from);

than it will be posible.