Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 843
  • Last Modified:

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
0
zolf
Asked:
zolf
2 Solutions
 
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
 
Kelvin SparksCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
Anthony PerkinsCommented:
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
 
zolfAuthor Commented:
thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now