0

By accident I made a column that wasn't null friendly, even though I saw an error I tried a few times to add a null; didn't work.

But now my ID_Phisical that was my primary key is now, well weird. Do I have to create a new table or is there a way to just revert it back to 1 instead of 20?

enter image description here

Dale K
  • 25,246
  • 15
  • 42
  • 71
John P
  • 1
  • 2
    `TRUNCATE` also resets the identity seed (and also removes all data from the table) – Pred Dec 01 '21 at 20:25
  • 5
    The value of your identity shouldn't matter. If it matters then you probably shouldn't be using an identity. I would also suggest you spell Physical correctly or it will drive you nuts. And NEVER store the age of a person unless it is for historical purposes (like their age at the last visit or similar). Instead store the birthdate and calculate age when needed. – Sean Lange Dec 01 '21 at 20:28
  • 2
    Your `IDENTITY` isn't "weird", it's working as intended. The value of the `IDENTITY` is incremented every time you attempt to `INSERT` a row, it doesn't matter if that `INSERT` fails. You attempted to `INSERT` multiple rows that failed your constraints (for the column to not be `NULL`) and so the row wasn't inserted, but the `IDENTITY` value was still used, as it was supposed to be. Nothing weird at all. In fact, it would be weird if the value of your `IDENTITY` didn't skip the values for the failed `INSERT` statements. – Thom A Dec 01 '21 at 20:35

1 Answers1

0

For change reset identity, use command

DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
Dale K
  • 25,246
  • 15
  • 42
  • 71
Mostafa Baghinzadeh
  • 1
  • 3