CODEWITHSUNDEEP
c#sqlsql-server-2005
user175084
user175084

Reputation: 4630

delete records from 2 tables

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();

Upvotes: 1

Views: 1842

Answers (4)

HLGEM
HLGEM

Reputation: 96552

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.

Upvotes: 1

Ed Harper
Ed Harper

Reputation: 21495

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).

Upvotes: 0

RC1140
RC1140

Reputation: 8653

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.

Upvotes: 0

Lukáš Lalinský
Lukáš Lalinský

Reputation: 41306

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.

Upvotes: 0

Related Questions