T-sql – TSQL: How to DELETE in cascade without altering table / indexes

cascadet-sql

I have inserted some data in a table where I should not have. I am trying to delete the data, but it has a 1:M relationship and I need to also delete data that references this one.

I do not want to alter the tables / indexes, I just need this instruction to cascade:

-- this alters the CONSTRAINT -- not an option:
ALTER TABLE T2
ADD CONSTRAINT fk_employee
FOREIGN KEY (employeeID)
REFERENCES T1 (employeeID)
ON DELETE CASCADE;

Also I would like (read – prefer) not to change the IDE GUI settings.

Best Answer

Perhaps simply do it manually with two deletes, in a transaction to ensure consistency:

BEGIN TRANSACTION
DELETE T2 WHERE employeeID IN (SELECT employeeID FROM T1 WHERE <clauses_to_find_the_records_to_delete>
DELETE T1 WHERE <clauses_to_find_the_records_to_delete>
COMMIT TRANSACTION

You might want to add that FK constraint anyway, with or without the cascade options (I very much prefer without cascade, so still needing the manually ordered deletes, it is very convenient in some cases but adds dangers...), if it is correct for your data model.

Related Question