delete records from 2 tables

Question

I have to write a query to delete form 2 tables

DELETE FROM [Policies],[BackupSpec] WHERE [PolicyID] = @original_PolicyID

PloicyID is PK in Policies and FK in Backupspec

any suggestions??

   SqlConnection conn = new SqlConnection();
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["SumooHAgentDBConnectionString"].ConnectionString;
        string sql = "DELETE FROM [Policies],[BackupSpec] WHERE [PolicyID] = @original_PolicyID";
        string sql1 = "DELETE FROM [Backupspec] WHERE [PolicyID] = @original_PolicyID";
        SqlCommand cmd = new SqlCommand(sql, conn);
        SqlCommand cmd1 = new SqlCommand(sql1, conn);

        cmd.Parameters.AddWithValue("@original_PolicyID", item);


        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

Answer 1

Delete from one table at a time and use transactions to make it atomic. Or define the FK as ON DELETE CASCADE and delete only from the parent table.

Answer 2

The cleaner way to do it is just have 2 separate queries to delete from the policies and backup spec table. The only reason i would do this is because

1).Its easier for other people debugging by code to understand whats happening.

2).The is usually managed by a manager class which aids in separation of code.

Answer 3

Look at here,same question : Deleting from 2 tables at the same time?

Answer 4

Your code chould work if you carry out the delete from Backupspec first, then delete from Policies.

The FK is preventing you from removing the Policies record first (that's the point of Referential Integrity).

Answer 5

You cannot delete from two tables at once. This is a limit of SQl Server and thus you have no choice but to send two delete statements or to define cascade delete on the parent record. I recommend the two statements rather than cascade delete because cascade delete can cause locking problems if the delete involves many records. Remember cascade delete affects any deletes from any source in the tables involved and so even if you are only deleting one record ata time, it would also take affect when someone else at some point in the future needs to delete a million records. It will also take longer and longer and tie up more tables as the number of child tables increases.