sql - doctor schedulering design SQL server

Question

Im trying to run this line of code to create a database to schedule doctor.

EmployeeID int primary key not null,
DPhone int not null,
DFirstName nvarchar (20) not null,
DLastName nvarchar (25) not null,
Specialization nvarchar (100) not null,
Cost int not null);

create table schedule(
DEmployeeID int not null, foreign key (DEmployeeID) references doctor (EmployeeID),
SDay nvarchar (9) not null,
SDate date not null,
STime time not null,
BookingStatus char not null,
primary key (DEmployeeID, SDate, STime),
PMedicalID int, foreign key (PMedicalID) references patient (MedicalID));



create table medRecords (
prescription nvarchar (100),
diagnosis nvarchar (100) not null,
Pdescription text not null,
medRecID int not null primary key,
MedicalID int, foreign key (MedicalID) references patient (MedicalID),
SDate date not null, foreign key (SDate) references schedule (SDate),
EmployeeID int not null, foreign key (EmployeeID) references doctor (EmployeeID))

This is the error

There are no primary or candidate keys in the referenced table 'Schedule' that match the referencing column list in the foreign key 'FK__MedRecord__SDate__3D5E1FD2'.

The Sdate is already a primary key so i don't understand why i get this error.

edit I already have a patiens table created

标签： sqlsql-server