CREATE TABLE patient
(
patientid INT NOT NULL,
patientname VARCHAR(100),
patientphone VARCHAR(100),
CONSTRAINT PK_patient PRIMARY KEY (patientid)
)
CREATE TABLE doctor
(
doctorid INT NOT NULL,
doctorname VARCHAR(100),
CONSTRAINT PK_doctor PRIMARY KEY (doctorid)
)
CREATE TABLE drug
(
drugname VARCHAR(100),
drugid INT NOT NULL,
CONSTRAINT PK_drug PRIMARY KEY (drugid)
)
CREATE TABLE visit
(
visitid INT NOT NULL,
visitdate DATE,
visitreason VARCHAR(200),
doctorid INT REFERENCES doctor (doctorid),
patientid INT REFERENCES patient (patientid),
PRIMARY KEY (visitid, doctorid, patientid)
)
CREATE TABLE druglinkingtable
(
drugid INT REFERENCES drug (drugid),
visitid INT REFERENCES visit (visitid),
PRIMARY KEY (drugid, visitid)
)
Getting error in druglinkingtable:
There are no primary or candidate keys in the referenced table ‘visit’ that match the referencing column list in the foreign key ‘FK__druglinki__visit__44FF419A