Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue with truncation due to FOREIGN KEY constraint. Expand / Collapse
Author
Message
Posted Thursday, June 4, 2009 12:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 24, 2011 10:48 AM
Points: 1, Visits: 6
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.


Post #729203
Posted Thursday, June 4, 2009 12:30 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:01 PM
Points: 3,843, Visits: 3,836
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 - by Jeff Moden
Post #729212
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse