Tech

Trying to create a linking table [closed]

Ervin 
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

Related Posts