c# - DELETE 语句与 REFERENCE 约束冲突

Question

在我的项目中，我用两个数据集填充了两个数据库表。我用来填充这些数据库表的方法如下所示:

SqlCommand sqlCommand = new SqlCommand();            
SqlCommand command;
using (SqlConnection conn = new SqlConnection(strConn))
{
    using (SqlCommand cmd = new SqlCommand(strQuery, conn))
    {
        using (SqlCommand cmdReset = new SqlCommand("DBCC CHECKIDENT('Doctor', RESEED, 0)", conn))
        {
            using (SqlCommand cmdUnCheck = new SqlCommand("alter table [dbo].[Doctor] nocheck constraint all",conn))
            {
                using (SqlCommand cmdCheck = new SqlCommand("alter table [dbo].[Doctor] with check check constraint all",conn))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    conn.Open();
                    SqlTransaction sqlTransaction = conn.BeginTransaction();
                    cmd.Transaction = sqlTransaction;
                    cmdReset.Transaction = sqlTransaction;
                    cmdUnCheck.Transaction = sqlTransaction;
                    cmdCheck.Transaction = sqlTransaction;
                    try
                    {
                        cmdUnCheck.ExecuteNonQuery();
           /*ERROR*/    cmd.ExecuteNonQuery();
                        cmdReset.ExecuteNonQuery();//deleting database table data
                        foreach (DataRow dr in ds.Tables[0].Rows) //Inserting new data into the Database table
                        {
                            command = new SqlCommand(query.createDoctorRow("Doctor",
                            dr[1].ToString(), dr[2].ToString(), Convert.ToInt64(dr[3]), Convert.ToInt32(dr[4])), conn, sqlTransaction);
                            command.ExecuteNonQuery();
                        }
                        cmdCheck.ExecuteNonQuery();
                        sqlTransaction.Commit();
                        conn.Close();
                    }
                    catch (Exception e)
                    {
                        sqlTransaction.Rollback();
                        throw;
                    }
                }
            }
        }
    }                
}

上面的代码在第二个数据库表为空之前工作正常，但是当我在其中插入一些数据然后运行代码时，VS 显示错误 -->

The DELETE statement conflicted with the REFERENCE constraint "FK_Dr_ID". The conflict occurred in database "Hospital", table "dbo.Patient", column 'Doctor_ID'. The statement has been terminated.

这似乎很清楚，外键限制了查询的发生，但由于我是初学者，不知道该怎么做，所以我请求你的帮助..

在上面的代码中，我引用这个 SO answer 做了修改。但没有喜悦，错误仍然存​​在于同一点。

可选:有没有办法减少代码中的 using 语句？

Answer 1

您不能删除 DOCTOR 表中的数据，因为您通过 DOCTOR 的 Id 与 Patient 表相关联。