Click here to Skip to main content
14,331,729 members
Rate this:
Please Sign up or sign in to vote.
See more:
Is there any way to truncate a table with foreign key constraint without disabling the constraint
Posted
Rate this:
Please Sign up or sign in to vote.

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).
   
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!
Rate this:
Please Sign up or sign in to vote.

Solution 3


  1. 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.


  2. 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
   
v6
Rate this:
Please Sign up or sign in to vote.

Solution 2

That creates inconsistency in DB, and it is not allowed, either you have to disable or drop constraint then truncate...
   
Comments
King Fisher 17-May-14 3:25am
   
we cant perform Truncate Operation

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