Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table called Folder with 3 fields: Id (PK), Name, UserId (FK)

and another table called User with two fields: Id (PK), UserName

i am trying to run this sql:

truncate table Folder
truncate table User

i get the following error

Cannot truncate table 'Folder' because it is being referenced by a FOREIGN KEY constraint.
Cannot truncate table 'user'   because it is being referenced by a FOREIGN KEY constraint.

...even after deleting all the records in both tables i get this. why am i not allowed to truncate my tables?

how can i reset the seeds of the PK's easily without using truncate if there is now way to run a truncate on pk/fk relationship tables?

Edit: DB diagram added enter image description here

share|improve this question
    
It looks like Folder is also being used as an FK in another table. That table would need to be dumped first? –  Kevin Peno May 2 '11 at 19:01
    
all the tables in the DB have no records. –  kacalapy May 2 '11 at 19:07

1 Answer 1

up vote 7 down vote accepted

To reseed, use DBCC CHECKIDENT

DBCC CHECKIDENT ('table', RESEED, 1)

Or you'd have to drop the FK, truncate, then recreate the FK.

This is by design: TRUNCATE TABLE is simple page deallocation and constraints are not checked row by row. This limitation means you can't truncate when there are FKs.. even if you disable the FK.

share|improve this answer
    
thanks, but i dont see why i cant truncate tables. i am sure this is possible even with constraints being used. –  kacalapy May 2 '11 at 19:08
2  
I just told you why: there are foreign keys on the table. msdn.microsoft.com/en-us/library/ms177570.aspx –  gbn May 2 '11 at 19:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.