Click here to Skip to main content
15,917,481 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Posted
Updated 8-Jun-11 21:46pm
v7
Comments
walterhevedeich 9-Jun-11 0:36am    
1. Can you post the relevant script?
2. What error message are you getting?
A7mad Hegazy 9-Jun-11 0:46am    
I posted them
walterhevedeich 9-Jun-11 2:02am    
So your'e problem is making a relation on the relationship diagram? Is that right? What does C# have to do with it?
A7mad Hegazy 9-Jun-11 2:25am    
mmmmmmmmmmmm waiting for somebody to say why do u need this
because I use linq with this procedure in a winForm
but this not our problem

1 solution

You need to look at what you are trying to do, and logically go through what you would like to happen if an Appointment is deleted. You are trying to set the Constraint up as On Delete Cascade, but is this what you really want? This would mean that if an appointment is deleted, then the Doctor and Patient records would also be deleted. This cannot be really what you need. You should set both Constraints to On Update No Action, and just keep the Foreign Key relationship as an index, rather than a full Constraint.
 
Share this answer
 
Comments
A7mad Hegazy 9-Jun-11 3:26am    
I don't mean that
I mean when a patient or a doctor is updated or deleted
it make the same in appointments
example
when i update the column patient_ID in Patient
it is updated also in appointments
that what i want,but i can't make it cascade :(

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900