title: The DELETE statement conflicted with the REFERENCE constraint
body: <p>In my project I am filling two database tables with two DataSets. The method which I am using to fill those database tables is shown  below:</p>
  
  <pre><code>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;
                      }
                  }
              }
          }
      }                
  }
  </code></pre>
  
  <p>The above code was working fine till the second Database table was empty but when I inserted some data in it and then run the code, VS is showing error --> </p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>This seems clear that the foreign key is restricting the query to take place but as I am a beginner and dont know what to do I am asking your help..</p>
  
  <p>In the above code I made changes referring to this <a href="https://stackoverflow.com/a/1566552/1577396">SO answer</a> but no joy, the error still exists at the same point.</p>
  
  <p><em>Optional</em>: Is there anyway to reduce the <code>using</code> statements in my code?</p>