I have 3 tables (Patient,Doctor,Appointments)
patient has a primary key(Patient_ID)and a foreign key (Doctor_ID)
Doctor has a primary key(Doctor_ID)
Appointments has a primary key(Appo_ID)and two foreign keys(Patient_ID,Doctor_ID)
here is the problem,In the relationship diagram the two relations(Patient.Patient_ID,App.Patient_ID & Doctor.Doctor_ID,Appo.Doctor_ID) can't be cascade at once,
if one is cascade, the other show this error message
- Unable to create relationship 'FK_Appointments_Doctor'.
Introducing FOREIGN KEY constraint 'FK_Appointments_Doctor' on table 'Appointments' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.
and this is my procedure for updating
ALTER PROCEDURE UpdateDoc
@Doctor_ID INT,@Name NVARCHAR(50),@Salary MONEY,@Hire_Date DATETIME,@Dept_ID INT
AS
Update Doctor
Set
Doctor_ID=@Doctor_ID,
[Name] =@Name,
Salary=@Salary,
Hire_Date=@Hire_Date,
Dept_ID=@Dept_ID
Where Doctor_ID=@Doctor_ID
Return
If there any question for more specific, ask me