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

Question

I made some tables like this

create table Pembeli(
ID_pembeli number(16) constraint pm_id_p_pk_fk PRIMARY KEY,
Nama_pembeli varchar2(30) constraint pm_nama_pembeli_nn NOT NULL,
Nama_penumpang varchar2(30) constraint pm_nama_penumpang_nn NOT NULL,
Nama_kereta varchar2(30) constraint pm_nama_kereta_nn NOT NULL,
Kelas varchar2(10) constraint pm_kelas_nn NOT NULL
)

create table Kereta(
ID_Kereta number(3) constraint kr_id_kereta_pk_fk PRIMARY KEY,
Nama_Kereta varchar2(30) constraint kr_nama_kereta_nn NOT NULL,
Jam_keberangkatan TIMESTAMP default systimestamp,
Jam_tiba TIMESTAMP default systimestamp,
Stasiun_asal varchar2(20) constraint kr_stasiun_asal_nn NOT NULL,
Stasiun_tujuan varchar2(20) constraint kr_stasiun_tujuan_nn NOT NULL,
Harga number(7) constraint kr_harga_nn NOT NULL
)

create table Kelas(
ID_Pembeli number(16) constraint kl_id_pembeli_pk_fk PRIMARY KEY,
Nama_kelas varchar2(30) constraint kl_nama_kelas_nn NOT NULL,
Harga number (7) constraint kl_harga_nn NOT NULL
)

create table Tiket(
ID_Tiket varchar2(10),
ID_Pembeli number(16),
No_Duduk varchar(3) constraint tk_no_nn NOT NULL,
ID_Kereta number(3),
Kelas varchar2(10) constraint tk_kelas_nn NOT NULL,
Jumlah number(3) constraint tk_jml_nn NOT NULL,
CONSTRAINT tk_pk PRIMARY KEY(ID_Tiket,ID_Pembeli)
)

create table Kasir(
ID_Kasir varchar2(10),
Nama_kasir varchar2(30) constraint ks_nama_kasir_nn NOT NULL,
Uang number(7) constraint ks_uang_nn NOT NULL,
Harga number(7) constraint ks_harga_nn NOT NULL,
Kembalian number(7) constraint ks_kembalian_nn NOT NULL,
ID_Pembeli number(16),
CONSTRAINT ks_pk PRIMARY KEY(ID_Kasir,ID_Pembeli)
)

With these foreign keys

alter table Pembeli add constraint pm_fk1 FOREIGN KEY (ID_Pembeli) references kasir(ID_pembeli)

alter table Pembeli add constraint pm_fk2 FOREIGN KEY (ID_Pembeli) references tiket(ID_pembeli)

I can create all tables, but I get stuck when I tried to add constraint foreign key to table: I get an error when I run the alter table command. Can someone help me ?

Answer 1

The mistake is pretty clear: You are trying to make one column be a foreign key pointing to a table with a compound primary key (two columns). That can't work.

But, ID_Pembeli is already declared PK in the Pembeli table. It seems like you wanted the foreign keys to go the other way around: you want to alter tables kasir and ticket, to make ID_Pembeli foreign key in both tables, referencing table Pembeli. That would make more sense and will work. Just fix the ALTER TABLE statements.