如何使用多包SQL异常块处理外键冲突？ [英] How to handle foreign key violation with multicatch SQL exception blocks ?

问题描述

Hello Guys我正在使用C＃.net和MSSQL Server。我有两个表类别和药品，

药品表使用Category_ID作为外键。

问题：

i想要删除异常，当我删除在Medicine中引用的Category_ID时，根据异常层次结构（结尾的常规异常），以便我可以捕获其他异常。



我的尝试：



我只是通过常规异常捕获异常。

Hello Guys I m working with C#.net and MSSQL Server.I have two Tables Categories and Medicines,
Medicines Table is using Category_ID as Foreign Key.
Question:
i want to catch exception when i delete Category_ID which is being referenced in Medicine, according to exception hierarchy(General exception at end) so that i can catch other exceptions.

What I have tried:

I m just catching exception with General Exception.

推荐答案

如果我正确理解了你的问题，那你就试图抓住DELETE语句与您尝试删除未启用级联删除的外键约束引用的记录时SQL引发的REFERENCE约束错误冲突。



错误编号为 547 。假设您正在使用 System.Data.SqlClient 命名空间中的类来管理数据，您只需要捕获 SqlException ：

If I've understood your question correctly, you're trying to catch the "The DELETE statement conflicted with the REFERENCE constraint" error that SQL raises when you try to delete a record that's referenced by a foreign key constraint which doesn't have "cascade delete" turned on.

The error number will be 547. Assuming you're using the classes in the System.Data.SqlClient namespace to manage your data, you just need to catch a SqlException:

try
{
   ... Code to delete a record here ...
}
catch (SqlException ex) when (ex.Number == 547)
{
    ... Notify the user that the record is in use here ...
}

我不知道由于 Categories 表中删除 CategoryID wiki / Data_integrity>数据（数据库）完整性 [ ^ ]。



我强烈建议添加一点（ true / false ）字段（对于ex） ample Active ）到 Categories 表，如果 Category 是否可用。

I do not recommend to delete CategoryID from Categories table due to data (database) integrity[^].

I'd strongly recommend to add a bit (true/false) field (for example Active) to Categories table, which will be responsible for detection if Category is available or not.

这篇关于如何使用多包SQL异常块处理外键冲突？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！