My auto increment does not start with 1

Question

 CREATE TABLE DUser (
 [ID]           INT          NOT NULL,  [DUser_Id]  VARCHAR(45) NULL,
 [Name]         VARCHAR (70) NOT NULL,
 [DOB]          DATE         NOT NULL,
 [Password]     VARCHAR (30) NOT NULL,
 [Email]        VARCHAR (70) NOT NULL,
 [Phone_Number] INT          NOT NULL,
 [Gender]       VARCHAR (6)  NOT NULL,  PRIMARY KEY CLUSTERED ([ID] ASC) );


Create TRIGGER DUser_IDColumn    
ON  DUser    
AFTER  INSERT 
AS 
BEGIN
SET NOCOUNT ON;

UPDATE duserid SET    
DUser_ID = 'U' + LEFT('0000' + CAST(inserted.ID AS VARCHAR(10)), 5) FROM       DUser duserid

INNER JOIN inserted

ON duserid.ID = inserted.ID

END GO

The ID starts at U00000, when I want it to start from U00003, as there are pre-defined values. PLEASE I NEED HELP!!

When I Create an user account

The title suggests you expect your autoincrement to start a `1`. The screenshot shows it starting at `0` and in your question you state you want it starting at `3` ... which one is it? — Manfred Radlwimmer, Nov 28 '16 at 12:12
Also: The Insert trigger is ... well a bit weird. Do you expect `ID` and `DUser_Id` to be in sync? If yes: why bother keeping track of it? If not: What's the problem? Why would you *de-normalize* your database on purpose? — Manfred Radlwimmer, Nov 28 '16 at 12:13
And a third one, because I just noticed it on the screenshot: **Don't store passwords in your database!**. Store hashes and salts, but **never** the actual password! — Manfred Radlwimmer, Nov 28 '16 at 12:17
Possible duplicate of [How to set initial value and auto increment in MySQL?](http://stackoverflow.com/questions/1485668/how-to-set-initial-value-and-auto-increment-in-mysql) — Percy Pig, Jan 31 '17 at 06:22

score 1 · Answer 1 · edited Nov 28 '16 at 12:19

1

Looks like you need to reseed your AUTO-INCREMENT like below using ALTER statement

ALTER TABLE DUser AUTO_INCREMENT = 1

From your posted code, I don't see that it's a autoincrement column at all. Then there is no way you can expect it to start from 1. It's looks like custom identity column.

CREATE TABLE DUser (
 [ID]           INT          NOT NULL

edited Nov 28 '16 at 12:19

Manfred Radlwimmer

13,257
13
53
62

answered Nov 28 '16 at 12:06

Rahul

76,197
13
71
125

How do i go about to change it? I want to auto increment the DUser_ID whenever a new one is created. Currently I have two predefined users, U00001, U00002. And when I create, it become U00000 instead of U00003 – Percy Pig Nov 28 '16 at 12:12
@PercyPig, use a `ALTER` statement. Search in stack-overflow ... you will get plenty existing answer. – Rahul Nov 28 '16 at 12:13

score 0 · Answer 2 · answered Nov 28 '16 at 12:12

Can't you, in you script where you create the table "DUser" put something like

`AUTO_INCREMENT=3`

? So It will be something like

CREATE TABLE DUser (
[ID]           INT          NOT NULL,  
[DUser_Id]  VARCHAR(45) NULL,
[Name]         VARCHAR (70) NOT NULL,
[DOB]          DATE         NOT NULL,
[Password]     VARCHAR (30) NOT NULL,
[Email]        VARCHAR (70) NOT NULL,
[Phone_Number] INT          NOT NULL,
[Gender]       VARCHAR (6)  NOT NULL,  PRIMARY KEY CLUSTERED ([ID] ASC) )
ENGINE=InnoDB AUTO_INCREMENT=3;

My auto increment does not start with 1

2 Answers2