-3

CourseID is auto incrementing. If I have rows with identities of 16, 17, and 18 and delete all of them, 19 is the next ID generated. I want to set it back to 16 where the last row was deleted.

Nicole DesRosiers
  • 688
  • 7
  • 20
Hassan
  • 303
  • 2
  • 8
  • 25
  • 2
    The value of a surrogate key is meaningless. Yes, there are gaps in the values. Whether "Physical Chemistry" is courseid 1486902 or 1, you should not base any logic on the ID associated to them (id 1 is *always* P. Chem). Rather, use the business key and ignore gaps and islands in ids. Otherwise, remove the identity property and manage the values yourself – billinkc Jul 30 '13 at 21:01

5 Answers5

2

When you delete records identity seed doesn't get reset. Check this tutorial: http://www.howtogeek.com/howto/database/reset-identity-column-value-in-sql-server/

DBCC CHECKIDENT (your_table_name, RESEED, 0)

Also please note this rather strange behavior DBCC CHECKIDENT Sets Identity to 0

Community
  • 1
  • 1
empi
  • 15,755
  • 8
  • 62
  • 78
1

That is not how an IDENTITY field works. You would need to perform that logic yourself or reset the identity seed.

Cam Bruce
  • 5,632
  • 19
  • 34
1

This is not how the identity field works, and indeed, resetting an identity field when your solution is live would usually be a bad idea.

If you have any other tables elsewhere in your database referring to rows in this table (or audits, etc), by resetting the CourseID you could effectively be pointing them at different courses when the table is repopulated.

If you really need to reset your seed, you can do so with the following command:

DBCC CHECKIDENT (Course_Table, RESEED, 0)

Steve Mayne
  • 22,285
  • 4
  • 49
  • 49
0

If this is SQL Server database and you truly want to clean out the table, you might be able to use TRUNCATE TABLE instead. That will not only remove all the rows, it will reset the IDENTITY value as well.

mwigdahl
  • 16,268
  • 7
  • 50
  • 64
0

WHy on earth would you want them to not get skipped if you rollback the transaction or delte them. That is in there by design to allow mulitple transactions. It would be a very bad idea to try to change this witha manual process and would put your data integrity at risk. It is a good thing that they skip not a bad thing.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • I feel like a student answering the teachers question but one good reason I can think of why you would want an identity column to not get skipped is when you are developing or testing a new table before moving it production. You want to make schema and data modifications. Not being a SQL guru I've often needed to renumber all rows multiple times while testing data. Maybe too simplistic and obvious (and why no one responded) but for the newbies out there.... – Doreen Aug 23 '18 at 17:47