Delete many records from table A and B with one FK to table B

I have 2 tables: A and B

A contains the following columns:

Id [uniqueIdentifier] -PK
checkpointId [numeric(20,0)]

B contains the following:

Id [uniqueIdentifier] – PK
A_id (FK, uniqueIdentifier)

B has a reference to A from A_id column (FK)

The question: I want to delete all records from table A that their checkpoint_id is less than X:

delete from CheckpointStorageObject where checkpointIdentifierIdentifier <= 1000

But I can’t do it since "The primary key value cannot be deleted because references to this key still exist"

I tried to delete first from B table without a join:

DELETE FROM CheckpointToProtectionGroup 
WHERE EXIST (SELECT * from CheckpointStorageObject 
             WHERE CheckpointStorageObject.CheckpointIdentifierIdentifier <= 1000)

But it didn’t work.

How can I do it? Is it possible to delete from both table with one execute commands?

The resulted deleted records may be very big – more than 30K records in each table.

Answer

Try this:

First delete from tableB:

delete from tableB where A_id IN (Select Id from tableA where checkpointId <= 1000)

And then delete from tableA:

delete from tableA where checkpointId <= 1000

Advertisement

Answer