Link to home
Start Free TrialLog in
Avatar of Zolf
ZolfFlag for United Arab Emirates

asked on

Cannot truncate table because it is being referenced by a FOREIGN KEY constraint

Hello there,

How can I truncate a table which has foreign key constraints. I am in development and I need to truncate the tables.is there some easy way to this.

cheers
Zolf
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Use Delete FROM... instead. To use Truncate the FK has to be dropped. DELETE will allow a delete providing the records being deleted do not cause any RI issue.


Kelvin
Avatar of Zolf

ASKER

but the issue is that that delete will not start the pk from 1. I want the pk to be reset too which truncate does it for you. by the way what do you mean "the records being deleted do not cause any RI issue."
SOLUTION
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you want to persist with truncate, then you have to drop each FK, perform the truncate, then recreate the FK


Kelvin
Note that the process to drop/recreate of constraint can entirely be scripted as well as the truncate itself.  You can generate entirely the drop/recreate of constraints.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zolf

ASKER

thanks