Truncate table with foreign key constraint

Question

0.00/5 (No votes)

See more:

SQL-Server

Is there any way to truncate a table with foreign key constraint without disabling the constraint

Posted 22-Jun-12 3:45am

revanth.crazy

Add a Solution

3 solutions

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)

Tim Corey · Answer 1 · 2012-06-22T03:46:00

Solution 1

The simple answer is no, you cannot. The whole point of constraints is that it constrains you to do the right thing (handle the soon-to-be-orphaned data before deleting the rows).

Posted 22-Jun-12 3:46am

Tim Corey

Comments

Manfred Rudolf Bihy 22-Jun-12 10:42am

Sorry, but you are wrong. The table t1 which has a foreing key constraint (towards table t2) can be deleted from all you want, unless of course there were another table t3 involved which would reference t1.

Tim Corey 22-Jun-12 10:57am

I think you are reading this question wrong. The OP asked about a table with a foreign key restraint where the restraint didn't have to be dropped. This indicates that the table is "referenced by a foreign key restraint". The wording wasn't perfect, but I think the meaning was clear.

Manfred Rudolf Bihy 22-Jun-12 11:00am

I tried to dissect this in my solution. If one reads the question as it is your solution is wrong.
If OP had meant to write:
"Is there any way to truncate a table that is referenced by a foreign key constraint without disabling the constraint"

Then I do agree to some extent.

Cheers!

Manfred Rudolf Bihy · Answer 2 · 2012-06-22T04:39:00

If your question really is:

"Is there any way to truncate a table with foreign key constraint without disabling the constraint"
Then my answer would be this:
Why should it be a problem if a table Table1 that references content of another table called Table2 is truncated. It might be a problem to truncate Table2 as the stuff to be deleted from Table2 might well be still referenced from Table1.
So going by your question as it stands right now I say go ahead and truncate all you want, that is unless there was some third table Table3 involved that would have a foreign key constraint towards Table1.

A foreign key constraint in the scenario I mentioned above will keep you from inserting records into Table1, if the fields that make up the foreign key reference towards Table2 do not have values that are associated with a record in Table2.
If your question was meant to be this:

"Is there any way to truncate a table that is referenced by a foreign key constraint without disabling the constraint"
Then the answer would be: It depends. If the data to be truncated was already referenced from another table with a foreing key constraint, it would be impossible as the data integrity would be violated. Data that is referenced by a foreign key constraint is protected until either the constraint is lifted or the dependent data has been deleted.

On the other hand if the data to be deleted was not yet referenced by any entries in any other table with a foreign key constraint towards this table, it would not pose a problem.

Regards,

Manfred

kishhr · Answer 3 · 2012-06-22T03:51:00

Solution 2

That creates inconsistency in DB, and it is not allowed, either you have to disable or drop constraint then truncate...

Posted 22-Jun-12 3:51am

kishhr

Comments

King Fisher 17-May-14 3:25am

we cant perform Truncate Operation