Truncate table error

• David Kranes

  SSCrazy

  Points: 2523

  More actions

  May 19, 2008 at 1:15 pm

  #190861

  Hello, I am trying to truncate a table that has foreign key relationships. I have 2 tables one has the primary key and the other has a fk relationship. When I alter the fk on the child table to NOCHECK I still can NOT truncate the parent table. Here is what is happening:

  -- Parent table

  ALTER TABLE OUTLET_GROUP NOCHECK CONSTRAINT ALL

  go

  -- Child table

  ALTER TABLE CSAROLE NOCHECK CONSTRAINT ALL

  go

  TRUNCATE TABLE OUTLET_GROUP

  go

  -- Error Message.

  Msg 4712, Level 16, State 1, Line 1

  Cannot truncate table 'OUTLET_GROUP' because it is being referenced by a FOREIGN KEY constraint.

  If I drop the fk on CSAROLE is works fine. Is there a way in SS 2005 to completely disable these constraints so I can truncate and reload the parent table? This works fine in Oracle. This is part of a daily datafeed process we run in Oracle and I need to support SS 2005 as well.

  Thank you kindly for your help.

  David

  Best Regards,

  ~David

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  May 19, 2008 at 1:28 pm

  #817419

  You must drop the FK and then add it again after the TRUNCATE.

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden