Risks/drawbacks of using ALTER TABLE NOCHECK CONSTRAINT FK_XXX on production system ???

• jayrocks20

  SSC Veteran

  Points: 206

  More actions

  October 2, 2014 at 8:41 am

  #100193

  I have a selfreferenced foreignkey table . Deleting records from this table is complicated.

  I am using a CTE to fetch all child records to be deleted together to avoid foreign key constraint error however it has performance issues and locks the table for long.

  as an alterative I though of :

  ALTER TABLE

  NOCHECK CONSTRAINT FK_XXX

  DELETE FROM TABLE WHERE XXX

  ALTER TABLE CHECK CONSTRAINT FK_XXX

  I want to know the Risks/ drawbacks involved in using this ALTER TABLE NO CHECK CONSTRAINT on production servers.

  Thanks..

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736478

  More actions

  September 25, 2015 at 7:19 am

  #1829434

  The immediate risk is that you write a poor query and delete the wrong data.