Issue with truncation due to FOREIGN KEY constraint.

• techierohitdev

  Grasshopper

  Points: 11

  More actions

  June 4, 2009 at 12:23 pm

  #131021

  I want to truncate data.

  1. So i do ALTER TABLE table1 NOCHECK CONSTRAINT ALL

  2. Then i do TRUNCATE TABLE table1

  3. Then i get an error java.sql.SQLException: Cannot truncate table 'table1 ' because it is being referenced by a FOREIGN KEY constraint.

  The table has cyclic reference.

  i.e it has an primary key id which is an foreign key in the next row.

  Any help is greatly appreciated.

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  June 4, 2009 at 12:30 pm

  #1004854

  SQL Server will not allow you to use the TRUANCATE TABLE command on a table referenced by a foreign key. You have 2 options.

  1. Drop foreign key, truncate table, re-create foreign key.

  2. Leave foreign key alone, use DELETE instead.

  John Rowan

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