Is there a way to make another value inside ID Primary key?

Question

i have an primary key who generated itself when i tried to insert some data, but the problem is it will be generated the last one who i deleted before

I wan to make an input when i input the new data the generated id will be 17 not 72 and so on, i'm using SQL SERVER 2008, help :{

There is no easy way to do this. I would suggest you structure your application to not care what that identifier is as it relates to the sequence with the other records in the table. That is how most apps do/should work when using [surrogate keys](https://en.wikipedia.org/wiki/Surrogate_key). — Igor, Jul 30 '20 at 11:41
You can reset the IDENTITY value BUT a primary key's purpose is to uniquely identify the row within the table. It doesn't need to be without gaps or be pleasant to your eyes. — Cetin Basoz, Jul 30 '20 at 11:42
Do not try to reset the IDENTITY, you will run into collision errors as soon as the next value reaches `72` based on the screenshot in your question. — Igor, Jul 30 '20 at 11:44
@Igor so is there no way i can't get out of that sequences key? :( — Ricky Reza Muhammad, Jul 30 '20 at 11:47
You don't need to, @RickyRezaMuhammad , so it's not that there's no way, but it's that you shouldn't. Identity's are just arbitrary (always ascending) numbers; that's all. If you want a sequential value, then use `ROW_NUMBER()` when selecting from your table. — Thom A, Jul 30 '20 at 12:19
To add to @Larnu's statement, an auto-incremental key is a so-called "surrogate key" as opposed to a "natural key". If you want to number your records incrementally and without gaps, create a trigger or computed column. You will still have gaps if you delete records. — CodeCaster, Jul 30 '20 at 12:26

score 0 · Answer 1 · answered Jul 30 '20 at 12:43

...but the problem is it will be generated the last one i deleted before

Do not expose your primary key. It's a bad idea.

Primary keys are internal unique row identifiers. They are not supposed to be sexy or good looking. If you are caring about its value, then it means you somehow want to expose its value to the external world, and that's a really bad idea; if you do this you'll be converting this key into a natural key somewhere else in a different application and that can become an expensive dependency to maintain in the short, medium, and long term.

Solution? If you really care about the format, numbering, and you want to expose the PK, then create a secondary key [column] on the table. This one can be generated automatically, with any value or format you prefer. And you can feel free to expose it to any other application, or even to the end user.

score -1 · Answer 2 · answered Jul 30 '20 at 12:21

-1

You can set IDENTITY_INSERT ON on the table and insert 17 and corresponding values. Read Identity_insert on MSDN. Also make sure that there should not any conflict occuring in future due to existing identity setup.

In that, you would have to reseed the identity.

You cannot update the identity column. You have to insert it fresh.

Sample code below

create table #table (id int identity(1,1), name varchar(30))

insert into #table
values('A'),('B')

SELECT * FROM #TABLE

SET IDENTITY_INSERT #table ON

insert into #table(id,name) values(8,'C')

SET IDENTITY_INSERT #table OFF

SELECT * FROM #table


+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  8 | C    |
+----+------+

answered Jul 30 '20 at 12:21

Venkataraman R

12,181
2
31
58

As I understand it, they have an application that writes to this table, and they want to "fill in" missing keys when inserting new records. This one-off query does not solve that problem; you'd have to run this for every insertion, which has many problems on its own. – CodeCaster Jul 30 '20 at 12:27
@CodeCaster, from what I understand from OP is, he wants to insert specific PK value in the identity column. I saw this as one time thing only. I did not understand, whether it is continuous need from his application. He has not mentioned it. – Venkataraman R Jul 30 '20 at 13:14

Is there a way to make another value inside ID Primary key?

2 Answers2