Truncate table data of SQL Server

Question

5.00/5 (1 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 10-Oct-13 0:42am

indrajeet jadhav

Updated 9-Nov-17 20:05pm

Add a Solution

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

4 solutions

Solution 4

Best way to Truncate Table

DELETE FROM TABLENAME
DBCC CHECKIDENT ('DATABASENAME.dbo.TABLENAME',RESEED, 0)

Posted 9-Nov-17 20:05pm

Member 13513536

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mehdi Gholam · Accepted Answer · 2013-10-10T00:46:00

Solution 1

Read the following : http://stackoverflow.com/questions/253849/cannot-truncate-table-because-it-is-being-referenced-by-a-foreign-key-constraint[^]

Posted 10-Oct-13 0:46am

Mehdi Gholam

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

Stephen Hewison · Accepted Answer · 2013-10-10T00:48:00

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.

Tom Marvolo Riddle · Accepted Answer · 2013-10-10T00:52:00

Solution 3

See this:

Disable All the Foreign Key Constraint in Database

Posted 10-Oct-13 0:52am

Tom Marvolo Riddle

Truncate table data of SQL Server

4 solutions

Solution 4

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us