Deleting a SQL row ignoring all foreign keys and constraints

Question

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

User · Answer

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

User · Answer

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 · Answer

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

User · Answer

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 · Answer

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    expand the database object  1  expand the dependant table object  2  expand the  Keys  folder right click on the foreign key choose the  Modify  option change the  Enforce Foreign Key Constraint  option to  No  close the  Foreign Key Relationships  window close the table designer tab when prompted confirm save changes run necessary delete queries 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

User · Answer

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 · Answer

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

User · Answer

On all tables with foreign keys pointing to this one  use   ALTER TABLE MyOtherTable NOCHECK CONSTRAINT fk name

User · Answer

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 · Answer

You could maybe disable and re-enable constraints   http   sqlforums windowsitpro com web forum messageview aspx catid 60 amp threadid 48410 amp enterthread y

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

Examples related to sql-server

Examples related to sql-server-2008