reference primary key from another table [closed]

I have to create 2 tables. the first one

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

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