I have to create various tables using queries. The code for the first 2 tables is as follows
CREATE TABLE patient(
SSN char(9) NOT NULL,
PID char(8) NOT NULL,
Address char(60) NOT NULL,
BirthDate DATE NOT NULL,
Gender char(1) NOT NULL,
Name char(20) NOT NULL,
BloodType VARCHAR(2) NOT NULL,
PRIMARY KEY(SSN, PID))
and
CREATE TABLE operation(
OID char(8) NOT NULL,
Name char(20) NOT NULL,
Location char(60) NOT NULL,
Time char(10) NOT NULL,
PRIMARY KEY(OID))
Both of those queries where used to create their tables. My problem comes in when i try to create a relationship table.
CREATE TABLE undergoes(
PID char(8) NOT NULL,
OID char(8) NOT NULL,
FOREIGN KEY(PID) REFERENCES patient(PID),
FOREIGN KEY(OID) REFERENCES operation(OID))
The goal of that table is simply to reference which patient undergoes which surgery, however when i try to run the code i keep getting "No unique index found for the referenced field of the primary table". Any help is greatly appreciated, i have tried googling this problem and i see a lot of people have it but i am not understanding exactly what it means or how i can fix it.
CREATE TABLE patient(
SSN char(9) NOT NULL,
PID char(8) NOT NULL,
Address char(60) NOT NULL,
BirthDate DATE NOT NULL,
Gender char(1) NOT NULL,
Name char(20) NOT NULL,
BloodType VARCHAR(2) NOT NULL,
PRIMARY KEY(SSN, PID))
and
CREATE TABLE operation(
OID char(8) NOT NULL,
Name char(20) NOT NULL,
Location char(60) NOT NULL,
Time char(10) NOT NULL,
PRIMARY KEY(OID))
Both of those queries where used to create their tables. My problem comes in when i try to create a relationship table.
CREATE TABLE undergoes(
PID char(8) NOT NULL,
OID char(8) NOT NULL,
FOREIGN KEY(PID) REFERENCES patient(PID),
FOREIGN KEY(OID) REFERENCES operation(OID))
The goal of that table is simply to reference which patient undergoes which surgery, however when i try to run the code i keep getting "No unique index found for the referenced field of the primary table". Any help is greatly appreciated, i have tried googling this problem and i see a lot of people have it but i am not understanding exactly what it means or how i can fix it.