DELETE operation contrary to REFERENCE constraint

Question

In my project, I populate two database tables with two DataSets. The following is the method that I use to populate these database tables:

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; } } } } } } 

This code worked fine until the second database table was empty, but when I inserted some data into it and then ran the code, VS shows an error →

The DELETE statement was contrary to the REFERENCE constraint "FK_Dr_ID". The conflict occurred in the database "Hospital", the table "dbo.Patient", in the column "Doctor_ID". Application completed.

It seems obvious that the foreign key restricts the request, but since I'm new and don't know what to do, I ask you to help.

In the above code, I made changes referring to this SO answer , but not joy, the error still exists at the same point.

Optional: Is there a way to reduce using statements in my code?

Answer 1

You cannot delete data in the DOCTOR table, since you have a relation to the patient table with the DOCTOR identifier.