I have to create 2 tables. the first one
x
CREATE TABLE orders
( order_id number(10) NOT NULL,
order_name varchar2(50) NOT NULL,
payment_id number(10) NOT NULL,
CONSTRAINT order_id PRIMARY KEY (order_id),
);
and when creating the second one I got this error ORA-02270: no matching unique or primary key for this column-list
CREATE TABLE payment
(
payments_id number(10) NOT NULL,
payment_name varchar(50) NOT NULL,
CONSTRAINT payments_id PRIMARY KEY (payments_id),
FOREIGN KEY (payments_id) REFERENCES orders(payment_id)
);
not sure what I’m doing wrong
please help
Advertisement
Answer
You need to reference a UNIQUE
or PRIMARY KEY
column. The payment_id
column does not have one of those constraints on it.
From the Oracle constraint documentation:
Foreign Key Constraints
A foreign key constraint (also called a referential integrity constraint) designates a column as the foreign key and establishes a relationship between that foreign key and a specified primary or unique key, called the referenced key.
Instead, you can add an order_id
column to your table:
CREATE TABLE orders(
order_id NUMBER(10) NOT NULL,
order_name VARCHAR2(50) NOT NULL,
CONSTRAINT orders__order_id__pk PRIMARY KEY (order_id)
);
CREATE TABLE payment(
payments_id NUMBER(10) NOT NULL,
payment_name VARCHAR2(50) NOT NULL,
order_id NOT NULL,
CONSTRAINT payment__payments_id__pk PRIMARY KEY (payments_id),
CONSTRAINT payment__order_id__fk FOREIGN KEY (order_id)
REFERENCES orders (order_id)
);
db<>fiddle here