That is the whole point of foreign key constraints: they stop you deleting data that is referred to elsewhere in order to maintain referential integrity.
There are two options:
- Delete the rows from
INVENTORY_ITEMS first, then the rows from
ON DELETE CASCADE for the in the key definition.
1: Deleting In Correct Order
The most efficient way to do this varies depending on the complexity of the query that decides which rows to delete. A general pattern might be:
SET XACT_ABORT ON
DELETE INVENTORY_ITEMS WHERE STOCK_ARTICLE IN (<select statement that returns stock_article.id for the rows you are about to delete>)
DELETE STOCK_ARTICLES WHERE <the rest of your current delete statement>
This is fine for simple queries or for deleting a single stock item, but given your delete statement contains a
WHERE NOT EXISTS clause nesting that within
WHERE IN might produce a very inefficient plan so test with a realistic data set size and rearrange the query if needed.
Also note the transaction statements: you want to make sure both the deletes complete or neither of them do. If the operation is already happening within a transaction you will obviously need to alter this to match your current transaction and error handling process.
ON DELETE CASCADE
If you add the cascade option to your foreign key then SQL Server will automatically do this for you, removing rows from
INVENTORY_ITEMS to satisfy the constraint that nothing should refer to the rows you are deleting. Just add
ON DELETE CASCADE to the FK definition like so:
ALTER TABLE <child_table> WITH CHECK
ADD CONSTRAINT <fk_name> FOREIGN KEY(<column(s)>)
REFERENCES <parent_table> (<column(s)>)
ON DELETE CASCADE
An advantage here is that the delete is one atomic statement reducing (though, as usual, not 100% removing) the need to worry about transaction and lock settings. The cascade can even operate over multiple parent/child/grand-child/... levels if there is only one path between parent and all the descendants (search for "multiple cascade paths" for examples of where this might not work).
NOTE: I, and many others, consider cascaded deletes to be dangerous so if you use this option be very careful to properly document it in your database design so you and other developers don't trip over the danger later. I avoid cascading deletes wherever possible for this reason.
A common problem caused with cascaded deletes is when someone updates data by dropping and recreating rows instead of using
MERGE. This is often seen where "update the rows that already exist, insert those that don't" (sometimes called an UPSERT operation) is needed and people unaware of the
MERGE statement find it easier to do:
DELETE <all rows that match IDs in the new data>
INSERT <all rows from the new data>
SET <col1> = source.<col1>
, <col2> = source.<col2>
, <colN> = source.<colN>
FROM <target_table> AS target JOIN <source_table_or_view_or_statement> AS source ON source.ID = target.ID
FROM <source_table_or_other> AS source
LEFT OUTER JOIN
<target_table> AS target
ON target.ID = source.ID
WHERE target.ID IS NULL
The problem here is that the delete statement will cascade to child rows, and the insert statement won't recreate them, so while updating the parent table you accidentally lose data from the child table(s).
Yes, you have to delete the child rows first.
There is another option:
ON DELETE CASCADE.
ON DELETE CASCADE can be dangerous, so use with care.
Side note: use
MERGE is not available) when you need an
UPSERT operation, not
INSERT to avoid falling into traps laid by other people using
ON DELETE CASCADE.