Linq-to-sql 删除具有 FK 约束的记录

发布于 2024-12-06 04:43:08 字数 371 浏览 0 评论 0原文

是否可以提交更改并忽略导致外键约束错误的删除？

假设我有对象 A 和 B，B 的主键是 A 中的外键。现在我更改一条记录 A，其中将 B 替换为另一个 B，并删除原来的 B。当删除的 B 在另一个中引用时，会出现错误记录A（或包含B作为外键的其他表）。是否可以忽略错误并让更改在数据库中进行，而不删除旧的 B？

A rec_a = (from a in db.As where a.id == some_id).First();
B rec_b_old = rec_a.B;
rec_a.B = null;
db.Bs.DeleteOnSubmit(rec_b_old);
rec_a.B = some_other_b;
db.SubmitChanges();

原文

Is it possible to SubmitChanges and ignore deletes that cause Foreign Key constraint errors?

Say I have object A and B. B's Primary Key is a Foreign Key in A. Now I change a record A in which I replace B for another B and delete the original B. An error will occur when the deleted B is referenced in another record A (or other tables containing B as Foreign Key). Is it possible to ignore the error and let the changes be made in the database, without deleting the old B?

A rec_a = (from a in db.As where a.id == some_id).First();
B rec_b_old = rec_a.B;
rec_a.B = null;
db.Bs.DeleteOnSubmit(rec_b_old);
rec_a.B = some_other_b;
db.SubmitChanges();

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

荭秂 2024-12-13 04:43:08

两次调用 SubmitChanges()：

A rec_a = (from a in db.As where a.id == some_id).First();
B rec_b_old = rec_a.B;
rec_a.B = null;
rec_a.B = some_other_b;
db.SubmitChanges();
db.Bs.DeleteOnSubmit(rec_b_old);
try
{
    db.SubmitChanges();
}
catch(SqlException) { } // Ignore failed delete.

第二次调用可能会失败，在这种情况下只需忽略它即可。可以尝试提交所有内容，找出失败的更新/删除，将其从待处理列表中删除并重试。然而它需要更多的代码，所以我认为不值得在这里这样做。

另一种解决方案是在 A 表上放置一个触发器，如果 B 记录是孤立的，则删除该记录。

Make two calls to SubmitChanges():

A rec_a = (from a in db.As where a.id == some_id).First();
B rec_b_old = rec_a.B;
rec_a.B = null;
rec_a.B = some_other_b;
db.SubmitChanges();
db.Bs.DeleteOnSubmit(rec_b_old);
try
{
    db.SubmitChanges();
}
catch(SqlException) { } // Ignore failed delete.

The second one might fail and in that case just ignore it. It is possible to try to submit everything, dig out the failing update/delete, remove it from the pending list and retry. However it requires far more code so I don't think it's worth to do it here.

Another solution is to put a trigger on the A table that deletes the B record if it is orphaned.

回复收藏 0 原文