Deleting a SQL row ignoring all foreign keys and constraints

Solution 1:

You can set the constraints on that table / column to not check temporarily, then re-enable the constraints. General form would be:

ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName

Then re-enable all constraints with

ALTER TABLE TableName CHECK CONSTRAINT ConstraintName

I assume that this would be temporary though? You obviously wouldn't want to do this consistently.

Solution 2:

Yes, simply run

DELETE FROM myTable where myTable.ID = 6850

AND LET ENGINE VERIFY THE CONSTRAINTS.

If you're trying to be 'clever' and disable constraints, you'll pay a huge price: enabling back the constraints has to verify every row instead of the one you just deleted. There are internal flags SQL keeps to know that a constraint is 'trusted' or not. You're 'optimization' would result in either changing these flags to 'false' (meaning SQL no longer trusts the constraints) or it has to re-verify them from scratch.

See Guidelines for Disabling Indexes and Constraints and Non-trusted constraints and performance.

Unless you did some solid measurements that demonstrated that the constraint verification of the DELETE operation are a performance bottleneck, let the engine do its work.

Solution 3:

Do not under any circumstances disable the constraints. This is an extremely stupid practice. You cannot maintain data integrity if you do things like this. Data integrity is the first consideration of a database because without it, you have nothing.

The correct method is to delete from the child tables before trying to delete the parent record. You are probably timing out because you have set up cascading deltes which is another bad practice in a large database.