Relationship Problem in SQL (Column can't be cascaded)

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开发者_如何学C my procedure for updating

ALTER PROCEDURE UpdateDoc
    @Doctor_ID INT,@Name NVARCHAR(50),@Salary MONEY,@Hire_Date DATETIME,@Dept_ID INT,@Old_ID int
AS

    Update Doctor
    Set 
        @Old_ID=Doctor_ID,
    Doctor_ID=@Doctor_ID,
    [Name] =@Name,
    Salary=@Salary,
    Hire_Date=@Hire_Date,
    Dept_ID=@Dept_ID
    Where Doctor_ID=@Old_ID
Return

If there any question for more specific, ask me

---

It's a cyclic dependency, why would you want it to cascade? And why are you updating the Doctor_ID strictly speaking you shouldn't ever be updating (setting) the primary key.

That you are filtering Where Doctor_ID=@Doctor_ID on the same value you are updating Set Doctor_ID=@Doctor_ID tells me you haven't tought this through.

---

I don't understand why you update Doctor_ID since you have it in the WHERE clause:

   Update Doctor
    Set 
    Doctor_ID=@Doctor_ID,
    ....
    Where Doctor_ID=@Doctor_ID

---

You have a logic problem. If you define a connection between patient and doctor, that you cannot define an appointment related to both. Choose just one of them and you'll get the other.

Try to model the database using the business logic of the real world example. So you have doctors and patients, ok? If the doctors are dedicated to a patient and one doctor will attend always the same patient than you create a foreign key relating them. Then for appointments, the appointment is related with a patient and because the patient is automatically related with a doctor, the appointment only needs the patient foreign key.