针对同一目标表/列创建两个级联外键
我有以下表格：
user (userid int [pk], name varchar(50))
action (actionid int [pk], description nvarchar(50))
被捕获关系的另一个表引用：
<user1> <action>'s <user2>.
我使用下表执行此操作：
userAction (userActionId int [pk], actionid int [fk: action.actionid], **userId1 int [fk ref's user.userid; on del/update cascade], userId2 int [fk ref's user.userid; on del/update cascade]**).
但是，当我尝试保存 userAction 表时，我收到错误，因为我有两个针对 user.userid 的级联 fk。
有什么办法可以解决这个问题还是我必须使用触发器？
I have the following tables:
user (userid int [pk], name varchar(50))
action (actionid int [pk], description nvarchar(50))
being referenced by another table that captures the relationship:
<user1> <action>'s <user2>.
I did this with the following table:
userAction (userActionId int [pk], actionid int [fk: action.actionid], **userId1 int [fk ref's user.userid; on del/update cascade], userId2 int [fk ref's user.userid; on del/update cascade]**).
However, when I try to save the userAction table i get an error because I have two cascading fk's against user.userid.
Is there any way to remedy this or must I use a trigger?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以使用触发器，但我认为最好实际编写删除操作。如果删除了大量记录，而我们不允许这样做，那么级联删除通常会导致性能不佳。您不能根据应用程序一次仅删除一条记录的事实来使用它们。如果该一条记录有数千条子记录怎么办？万一有几百万呢？级联删除或者通过触发器删除其他表是极其危险的行为。最好按照您想要的方式编写删除代码，如果子记录太多，甚至可以进行批处理。此外，存在子记录这一事实通常是您不应删除父记录的原因。例如，假设客户端 A 不再是活动客户端。但他过去从你那里买过价值数百万美元的东西。您真的想通过删除一百万个旧订单来搞乱会计记录吗？也许将记录标记为非活动是更好的选择。
You could use a trigger, but I think it is better to actually write the deletes. Cascading deletes are often the casue of poor performance if a large set of records gets deleted and we do not allow them here. You can't base their use on the fact that the application only deletes one record at a time. What if that one record has thousands of child records? What if it has millions? Cascaded delete or deleting other tables through a trigger is extremely risky behavior. It is better to write the code the delete exactly what you want, perhaps even doing batch processing if there are too many child records. Further the fact that there are child records is often a reson why you shouldn't delete the parent record. For instance suppose client A is no longer an active client. But he has bought millions of dollars of stuff from you in the past. Do you really want to mess up the accounting records by deleting a million old orders? Maybe marking the record as inactive is a better choice.