CODEWITHSUNDEEP
mysqlsql
Jose Partida
Jose Partida

Reputation: 23

MYSQL Create table statement giving me "non-unique" error ?

I am currently taking a SQL class in my college and am currently stuck on review question my professor assigned. I keep getting an error for my schedule table. I feel it has something to do with my foreign key call but I'm not 100% sure? For the Schedule table, she wants the doctorID to be the foreign key and date/clinic to be the primaries 

CREATE TABLE Doctor (
DoctorID numeric(3) not null,
Drname varchar (25) not null,
phonenum varchar (12) not null,
CONSTRAINT DoctorID_pk PRIMARY KEY (DoctorID)
);
CREATE TABLE Patient (
PatientNum numeric (5) not null,
SSNum numeric (9) not null,
PTName varchar (25) not null,
DOB numeric (8) not null,
PAddress varchar (40) not null,
DateAdmit numeric (8) not null,
Clinic varchar (30) not null,
CONSTRAINT PatientNum_pk PRIMARY KEY(PatientNum)
);

CREATE TABLE Treatment (
Treatmentcode numeric (6) not null,
Description varchar (50) not null,
CONSTRAINT Treatmentcode_pk PRIMARY KEY(Treatmentcode)
);

CREATE TABLE PatDocTreatment ( 
PatientNum numeric (5) not null,
DoctorID numeric (3) not null,
Treatmentcode numeric (6) not null,
DateofTreat numeric (8) not null,
Comments varchar (80) not null,
CONSTRAINT PK_PatDocTreatment PRIMARY 
KEY(Patientnum,DoctorID,Treatmentcode,DateofTreat),
FOREIGN KEY(PatientNum) REFERENCES Patient(PatientNum),
FOREIGN KEY(DoctorID) REFERENCES Doctor(DoctorID),
FOREIGN KEY(Treatmentcode) REFERENCES Treatment(Treatmentcode)
);
CREATE TABLE Schedule (
DoctorID numeric (3) not null,
SchDate numeric (8) not null,
Clinic varchar (30) not null,
Hoursworked numeric (3) not null,
CONSTRAINT PK_Schedule PRIMARY KEY(DoctorID,SchDate,Clinic),
FOREIGN KEY (DoctorID) REFERENCES Doctor (DoctorID)
FOREIGN KEY (CLINIC) REFERENCES Patient (Clinic)
);

Upvotes: 1

Views: 40

Answers (1)

Namandeep_Kaur
Namandeep_Kaur

Reputation: 378

You have possibly missed a comma between the last two foreign keys declarations in your schedule table.

Upvotes: 2

Related Questions