increasing id specified by group

Question

I have a table wich I want to let its ID be specified per person

This is what I have so far:

CREATE TABLE [dbo].[Duiken] (
[Duik_nr]          INT           IDENTITY (1, 1) NOT NULL,    // ID
[datum]            DATE          NULL,
[naam]             VARCHAR (50)  NOT NULL,                    // Person
[buddy]            VARCHAR (50)  NULL,
[locatie]          VARCHAR (50)  NULL,
[zicht]            INT           NULL,
[lucht_temp]       INT           NULL,
[water_temp]       INT           NULL,
[fles_Groote]      VARCHAR (50)  NULL,
[duik_tijd]        INT           NULL,
[max_duik_diepte]  INT           NULL,
[lucht_in]         INT           NULL,
[lucht_uit]        INT           NULL,
[lucht_verbruik]   AS            ([lucht_in]-[lucht_uit]),
[lood]             INT           NULL,
[oefenduik]        BIT           NULL,
[recreatieve_duik] BIT           NULL,
[zoetwater_duik]   BIT           NULL,
[zoutwater_duik]   BIT           NULL,
[stilstaand_water] BIT           NULL,
[stromend_water]   BIT           NULL,
[nacht_duik]       BIT           NULL,
[nitrox_duik]      BIT           NULL,
[zonnig]           BIT           NULL,
[bewolkt]          BIT           NULL,
[regen]            BIT           NULL,
[Opmerkingen]      VARCHAR (MAX) NULL,
PRIMARY KEY ([naam],[Duik_nr])

);

But when I add a new record on a new name it continues with the highest ID in the table.

the result im getting now

So the "test" person should also begin with ID 1 instead of 53.

something like this:

+----+------+
| id | Name |
+----+------+
|  1 | a    |
|  2 | a    |
|  1 | b    |
|  3 | a    |
|  1 | c    |
|  2 | b    |
+----+------+

When you insert "test" is it the only person in the table, or do you already have 52 other records? — Nicholas Hirras, Jan 03 '17 at 14:39
I don't believe you can use an auto increment field like this. I think you'll need to create a sequence and manually generate new values for ID when you need them? — Nicholas Hirras, Jan 04 '17 at 15:39

score 1 · Answer 1 · edited May 23 '17 at 10:30

1

Identity(1, 1) creates an AUTO_INCREMENT field. If the "test" person begins with ID1, then there will be a conflict between "test" and "Tim" and since you defined [Duik_nr] to be a primary key (there can't be duplicate values). All primary keys can only have one instance of each value. That's why you're getting the error that (2, TIM) already exists. There's already an ID 2 and a Name "Tim" in the table.

~~I believe you had previous records or your identity seed is set to a higher number, so the AUTO_INCREMENT is starting at a higher number.~~

Reset identity seed after deleting records in SQL Server

edited May 23 '17 at 10:30

Community

1
1

answered Jan 03 '17 at 14:37

Felix Guo

2,700
14
20

my identity seed is set to 1 and the only records i have are those from my other person – Tim Van De Ven Jan 03 '17 at 14:42
What happens when you add another person? ID becomes 54? – Felix Guo Jan 03 '17 at 14:44
when i add a new person the id becomes 54 – Tim Van De Ven Jan 03 '17 at 14:46
Wouldn't that mean the internal identity counter is currently at 53? What happens when you do what I suggested and reset it? – Felix Guo Jan 03 '17 at 14:48
yes it starts at 1 now but when i add another new person (and reset the identity again) and want to add new data to the test person the system says you can't do that because 2,Tim (ID,person) already excists – Tim Van De Ven Jan 03 '17 at 15:02

increasing id specified by group

1 Answers1