Deleting a SQL row ignoring all foreign keys and constraints

user_127257 0
Viewed 134435

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.

sql-server-2008 sql-server
11 Answers

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.

user_67646330

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.

user_56618650

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"
user_52080580

For the testing purpose only, I used the following command in MySQL to delete only one record from a table that has foreign key references.

SET FOREIGN_KEY_CHECKS = 0; -- Disabling foreign key checks before running the following query.
DELETE FROM table_name WHERE id = id_to_delete; -- Deleting a record from a table that has foreign key reference.
SET FOREIGN_KEY_CHECKS = 1; -- Enabling foreign key checks after running the above query.
user_36372690

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;
user_48874230
Related