SQL中的关系问题（列不能级联）
我有 3 张桌子（病人、医生、预约） 病人有一个主键（Patient_ID）和一个外键（Doctor_ID） 医生有一个主键(Doctor_ID) 约会有一个主键（Appo_ID）和两个外键（Patient_ID，Doctor_ID） 问题在于，在关系图中，两个关系(Patient.Patient_ID,App.Patient_ID & Doctor.Doctor_ID,Appo.Doctor_ID)不能同时级联， 如果一个是级联的，另一个会显示此错误消息
- 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.
，这是我的更新过程
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如果有任何更具体的问题，请问我
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,@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
ReturnIf there any question for more specific, ask me
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这是一个循环依赖，为什么要级联呢？为什么要更新
Doctor_ID严格来说，您不应该更新（设置）主键。您正在根据更新
Set Doctor_ID=@Doctor_ID的相同值过滤Where Doctor_ID=@Doctor_ID，这告诉我您还没有解决这个问题。It's a cyclic dependency, why would you want it to cascade? And why are you updating the
Doctor_IDstrictly speaking you shouldn't ever be updating (setting) the primary key.That you are filtering
Where Doctor_ID=@Doctor_IDon the same value you are updatingSet Doctor_ID=@Doctor_IDtells me you haven't tought this through.我不明白为什么你更新 Doctor_ID 因为你在 WHERE 子句中有它：
I don't understand why you update Doctor_ID since you have it in the WHERE clause:
你有逻辑问题。
如果您定义患者和医生之间的联系，则无法定义与两者相关的预约。只需选择其中之一，您就会得到另一个。
尝试使用现实世界示例的业务逻辑对数据库进行建模。
所以你们有医生和病人，好吗？如果医生专注于一名患者，并且一名医生将始终照顾同一名患者，那么您将创建一个与他们相关的外键。然后对于预约，预约与患者相关，并且由于患者自动与医生相关，因此预约只需要患者外键。
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.