0

I have a set of tables which I am going to clear out and upload new data into. One of these, Person has foreign keys pointing to it which prevent me from using TRUNCATE Table even though the other tables are empty.

I have used DELETE FROM after turning off the foreign key checks to get around this. This works except when I insert new values they start at the old value going up and I need them to reset to start at 1 again (or at least some consistent predictable value)

DBCC CHECKIDENT ([Person], RESEED, -1); or DBCC CHECKIDENT ([Person], RESEED, 0); I have seen suggested on other places for resetting the identity but give no useful results for me instead yielding:

Checking identity information: current identity value 'NULL'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

How can I either get truncate to work with the foreign keys pointing to my table OR using delete to clear the table get the primary key's auto increment to reset and start at 1?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
lathomas64
  • 1,612
  • 5
  • 21
  • 47
  • 1
    Did you try to insert a record after you run the `DBCC` command ? The identity value of `NULL` suggests the seed has been correctly reset. – Alex May 01 '15 at 20:30
  • Do you mean in the same connection? because I have inserted records after this by running the code I'm testing and got results starting off where the PersonId was originally. If it only has effect in the same connection that would explain the trouble I've had. – lathomas64 May 01 '15 at 20:34
  • I tried doing a delete and insert on the same connection and it worked, then I went back to the reseed and insert on separate connections and that worked too and I couldn't get it back to the broken state to verify what actually fixed it. I suppose there was some strange internal state the server got in because exactly what I was trying before that did not work is working fine now. – lathomas64 May 01 '15 at 20:54

2 Answers2

1

You cannot truncate a table, if there are foreign keys pointing to it. One way would be, to temporarily drop the foreign keys and after truncating recreate them.

Disabling foreign key constraint, still can't truncate table? (SQL Server 2005)

Community
  • 1
  • 1
agim
  • 1,841
  • 12
  • 19
0

The DBCC command should work. I have removed the square brackets and added single quotes:

DBCC CHECKIDENT ('dbo.Person', RESEED, 0);
Alex
  • 21,273
  • 10
  • 61
  • 73