使用FK约束和级联的最佳实践？ [英] Best practice for using FK constraints and cascading?

问题描述

在SQL Server 2008中，我已经定义了具有FK约束的父表和子表，其中子表中包含父键。我还启用了级联更新和删除。在VS2008中使用类型化数据集，我打开了"Both Relation和FK Constraint"。并将更新和删除规则设置为级联。

在我的层次网格中，我想重命名父主密钥，并且还可以重命名子项的外键并且可以正常工作。当我将数据集更新回SQL Server时出现问题我得到并发问题的原因是[现在明显]首先更新父记录，然后更新子记录，这会抛出exeption，因为服务器级联更新可能已经重命名儿童外键。

这里的最佳做法是什么？我尝试了各种技术，但没有发现真正令人满意的。理想情况下，我想在数据库中保留约束，但absolutley需要允许父键重命名反映在数据网格中的子项中。

任何建议都表示赞赏。谢谢，Dave

解决方案

为了确认，你已经添加了适当的约束条件了吗？

实例
ForeignKeyConstraint fk = new ForeignKeyConstraint（" fk"，ds.Tables [0] .Columns [" custid"]，
ds.Tables [1] .Columns [" custid"]）;
fk.DeleteRule = Rule.Cascade;
fk.UpdateRule = Rule.Cascade;
ds.Tables [1] .Constraints.Add（fk）;
ds.EnforceConstraints = true;

In SQL Server 2008 I have defined parent and child tables with a FK constraint where the child table has the parent key in it. I also have cascading updates and deletes enabled. Using a typed dataset in VS2008, I have turned on "Both Relation and FK Constraint" and set the update and delete rules to cascading.

In my hierachial grid, I want to rename the parent master key and have the child's foreign key also be renamed and that works. Problem comes when I update the dataset back to the SQL Server I get a concurrency issue for the [now obvious] reason that the parent record is updated first and then the child record is updated which throws the exeption since the server cascading update probbly already renamed the child foreign key.

What is the best practice here? I have tried various techniques but found nothing really satisfactory yet. Ideally I would like to keep the constraints in the database but absolutley need to allow a parent key rename reflect in the child in the datagrid.

Any suggestions appreciated. Thanks, Dave

解决方案

So to confirm, you have added the appropriate constraints?

//Example
ForeignKeyConstraint fk = new ForeignKeyConstraint("fk", ds.Tables[0].Columns["custid"],
        ds.Tables[1].Columns["custid"]);
fk.DeleteRule = Rule.Cascade;
fk.UpdateRule = Rule.Cascade;
ds.Tables[1].Constraints.Add(fk);
ds.EnforceConstraints = true;

这篇关于使用FK约束和级联的最佳实践？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！