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
zolfAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
DBCC CHECKIDENT (tablename, RESEED, 0)
Just be aware that in order to use this command you have to "be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role."
That is not always an option.
0
 
Kelvin SparksCommented:
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
0
 
zolfAuthor Commented:
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."
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kelvin SparksConnect With a Mentor Commented:
True,

But you could after the delete reset the identity seed.

DBCC CHECKIDENT (tablename, RESEED, 0)

would allow 1 to be the value of the identity seed for the next record in table 'tablename'

Kelvin
0
 
Kelvin SparksCommented:
If you want to persist with truncate, then you have to drop each FK, perform the truncate, then recreate the FK


Kelvin
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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.
0
 
zolfAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.