Click here to Skip to main content
14,333,565 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi guys
I want to delete data from table .but the table is already linked with other via Foreign key..So i am not able to delete data from table..So i dont wanna delete constraints of the table ..Is there other way to Truncate table data without dropping constraint

Its throws exception
Cannot truncate table because it is being referenced by a FOREIGN KEY constraint.

Need your help

Thank you
Posted
Updated 9-Nov-17 20:05pm
Comments
Member 12765037 15-Oct-16 10:09am
   
please explain briefly : -

select 1+'2' o/p:3
select 1+'+'+2 o/p:3
select 1+'-'+4 o/p:5

NOTE : -how this adding int variable with char variable please explain this scenario 2,3
Rate this:
Please Sign up or sign in to vote.

Solution 1

   
Comments
indrajeet jadhav 10-Oct-13 6:48am
   
@Mehdi Gholam Thnxx fr ur effort..but i dont wanna drop constraint..so there is another way??
Mehdi Gholam 10-Oct-13 6:54am
   
Read the link and you will see that they are disabled then reenabled.
indrajeet jadhav 10-Oct-13 7:55am
   
Thxx yaaa
Rate this:
Please Sign up or sign in to vote.

Solution 2

There are a number of ways to manage referential integrity.

You could disable the contraint:

http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql[^]

But you wouldn't be able to re-enable it until the data was fixed.

You can configure cascading deletes. This means any record linked through a foreign key would also be deleted.

http://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/[^]

But this will change database behaviour and allow parent records to be deleted where you may want the presence of a child to create a blocking action.

So in short, no you can't truncate a table without dropping the constraint, but SQL does have mechanisms for helping with referential integrity.
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 4

Best way to Truncate Table

DELETE FROM TABLENAME
DBCC CHECKIDENT ('DATABASENAME.dbo.TABLENAME',RESEED, 0)
   
Comments
CHill60 10-Nov-17 4:09am
   
And you will still get the error that the OP got. You have resurrected a 4 year old question, that already has 3 accepted solutions, to post an incorrect answer. Stick to answering new questions where the OP still needs help and make sure you read the question and any responses carefully first.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100