[sql-server] Deleting a SQL row ignoring all foreign keys and constraints

I have a row in a table. This row has an ID column referenced in a few other tables with millions of rows. The SQL statement to delete the row always times out. From my design, I know the row I wish to delete is never referenced any where else. Hence I would like SQL to ignore having to check all other tables for a foreign key reference to this row and delete the row immediately. Is there a quick way to do this in SQL 2008? Perhaps something along the lines of:

DELETE FROM myTable where myTable.ID = 6850 IGNORE CONSTRAINTS

Or something along those lines.

You could maybe disable and re-enable constraints:

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=48410&enterthread=y

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.

I wanted to delete all records from both tables because it was all test data. I used SSMS GUI to temporarily disable a FK constraint, then I ran a DELETE query on both tables, and finally I re-enabled the FK constraint.

To disable the FK constraint:

1. expand the database object [1]
2. expand the dependant table object [2]
3. expand the 'Keys' folder
4. right click on the foreign key
5. choose the 'Modify' option
6. change the 'Enforce Foreign Key Constraint' option to 'No'
7. close the 'Foreign Key Relationships' window
8. close the table designer tab
9. when prompted confirm save changes
10. run necessary delete queries
11. re-enable foreign key constraint the same way you just disabled it.

[1] in the 'Object Explorer' pane, can be accessed via the 'View' menu option, or key F8

[2] if you're not sure which table is the dependant one, you can check by right clicking the table in question and selecting the 'View Dependencies' option.

On all tables with foreign keys pointing to this one, use:

ALTER TABLE MyOtherTable NOCHECK CONSTRAINT fk_name

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.

I know this is an old thread, but I landed here when my row deletes were blocked by foreign key constraints. In my case, my table design permitted "NULL" values in the constrained column. In the rows to be deleted, I changed the constrained column value to "NULL" (which does not violate the Foreign Key Constraint) and then deleted all the rows.

You can disable all of the constaints on your database by the following line of code:

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

and after the runing your update/delete command, you can enable it again as the following:

EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

This is the way to disable foreign key checks in MySQL. Not relevant to OP's question since they use MS SQL Server, but google search results do turn this up so here's for reference:

SET FOREIGN_KEY_CHECKS = 0;

/ Run your script /

SET FOREIGN_KEY_CHECKS = 1;

See if this helps, This is for ignoring the foreign key checks. But deleting disabling this is very bad practice.

Temporarily disable constraints on a table T-SQL, SQL Server

MSSQL

  ALTER TABLE TableName NOCHECK CONSTRAINT ALL
  
  ALTER TABLE TableName CHECK CONSTRAINT ALL
  
  ALTER TABLE TableName NOCHECK CONSTRAINT FK_Table_RefTable
  
  ALTER TABLE TableName CHECK CONSTRAINT FK_Table_RefTable

ref

  DELETE FROM TableName
  
  DBCC CHECKIDENT ('TableName', RESEED, 0)

MySql

  SET FOREIGN_KEY_CHECKS = 0; -- Disable foreign key checking. 

  TRUNCATE TABLE [YOUR TABLE]; 

  SET FOREIGN_KEY_CHECKS = 1;