-
Why can't I create this table?
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?
-
Have you defined a primary key on order_line table ?
-
-
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.
You Have To Be Happy With What You Have To Be Happy With (KC)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|