Auto increment syntax errors

Question

Here's the code to my simple database:

CREATE TABLE [dbo].[User_Info] (
[Username]  NVARCHAR (50) NOT NULL,
[Password]  NVARCHAR (50) NOT NULL,
[Firstname] NVARCHAR (50) NOT NULL,
[Lastname]  NVARCHAR (50) NOT NULL,
[Email]     NVARCHAR (50) NOT NULL,
[Country]   NVARCHAR (50) NOT NULL,
[Phone]     NVARCHAR (50) NOT NULL,
[Gender]    NVARCHAR (50) NOT NULL,
[Admin]     INT           NULL,
PRIMARY KEY CLUSTERED ([Username] ASC)
);
SELECT GO * from User_Info;

Alter table User_Info
add column id int NOT NULL auto_increment Unique Key;

The error for this CREATE is:

Only 1 statement is allowed per batch, such as 'GO', might be required between statements

and the error for the column is:

Incorrect syntax near 'column'

I need an auto ID system so I can progress with my site, how can I fix this mess?

Additionally, specify the keyword `IDENTITY` instead of `auto_increment` in SQL Server. I'd post the proper code but cannot copy/paste your from the image. Always post code as text instead of images. — Dan Guzman, May 09 '20 at 13:18
@DanGuzman gotcha. I'll edit the post with a copy & pastable code. — kfir ezer, May 09 '20 at 13:23
The syntax you have for auto_increment is for mysql - not t-sql.. Are your tags wrong? — Peter Smith, May 09 '20 at 13:23
@PeterSmith I figured that there shouldn't be much of a difference, well I was clearly wrong... — kfir ezer, May 09 '20 at 13:34

Amin Golmahalleh · Accepted Answer · 2020-05-09T13:28:47.870

1

Below code worked for me:

CREATE TABLE user_Info (
   [admin] int IDENTITY(1,1) PRIMARY KEY,
   [Username]  NVARCHAR (50) NOT NULL,
   [Password]  NVARCHAR (50) NOT NULL,
   [Firstname] NVARCHAR (50) NOT NULL,
   [Lastname]  NVARCHAR (50) NOT NULL,
   [Email]     NVARCHAR (50) NOT NULL,
   [Country]   NVARCHAR (50) NOT NULL,
   [Phone]     NVARCHAR (50) NOT NULL,
   [Gender]    NVARCHAR (50) NOT NULL,
);

edited May 09 '20 at 13:28

answered May 09 '20 at 13:19

Amin Golmahalleh

3,585
2
23
36

will the "IDENTITY(1,1)" make it automatic? – kfir ezer May 09 '20 at 13:30
1

@kfir ezer Yes.The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. – Amin Golmahalleh May 09 '20 at 13:32
if you created your table.You need to set the identity to that column again.Follow this link: https://stackoverflow.com/a/61473194/5576498 – Amin Golmahalleh May 09 '20 at 13:36
1

I changed the [admin] to [id], and the PRIMARY KEY to NOT NULL, and it worked flawlessly. thanks – kfir ezer May 09 '20 at 13:37

score 1 · Answer 2 · answered May 09 '20 at 13:26

1

The code for T-SQL is:

CREATE TABLE dbo.UserInfo (
    UserName nvarchar(50) not null,
    PRIMARY KEY CLUSTERED (UserName ASC)
);
SELECT * FROM UserInfo;
GO
ALTER TABLE dbo.UserInfo ADD id int not null  IDENTITY(1,1);

However, it might be preferable to make id your primary key and add a constraint to user name if you want that to be unique.

answered May 09 '20 at 13:26

Peter Smith

5,528
8
51
77

I get an error in the "CREATE" word, and in the "id" word. – kfir ezer May 09 '20 at 13:32

score 1 · Answer 3 · answered May 09 '20 at 13:30

You need to add a batch separator, and remove column keyword, and replace auto_increment Unique Key with identity(1,1) unique

CREATE TABLE [dbo].[User_Info] (
[Username]  NVARCHAR (50) NOT NULL,
[Password]  NVARCHAR (50) NOT NULL,
[Firstname] NVARCHAR (50) NOT NULL,
[Lastname]  NVARCHAR (50) NOT NULL,
[Email]     NVARCHAR (50) NOT NULL,
[Country]   NVARCHAR (50) NOT NULL,
[Phone]     NVARCHAR (50) NOT NULL,
[Gender]    NVARCHAR (50) NOT NULL,
[Admin]     INT           NULL,
PRIMARY KEY CLUSTERED ([Username] ASC)
);
GO
SELECT * from User_Info;
GO
Alter table User_Info
add id int identity(1,1) not null unique;
GO

score 1 · Answer 4 · answered May 09 '20 at 13:33

When adding the column, specify IDENTITY along the a UNIQUE constraint specification to ensure unique values independent of the primary key. The example below also specifies a constraint name, which is a best practice instead of auto-generated constraint names.

CREATE TABLE [dbo].[User_Info] (
    [Username]  NVARCHAR (50) NOT NULL,
    [Password]  NVARCHAR (50) NOT NULL,
    [Firstname] NVARCHAR (50) NOT NULL,
    [Lastname]  NVARCHAR (50) NOT NULL,
    [Email]     NVARCHAR (50) NOT NULL,
    [Country]   NVARCHAR (50) NOT NULL,
    [Phone]     NVARCHAR (50) NOT NULL,
    [Gender]    NVARCHAR (50) NOT NULL,
    [Admin]     INT           NULL,
PRIMARY KEY CLUSTERED ([Username] ASC)
);

ALTER TABLE dbo.User_Info
    ADD id int NOT NULL IDENTITY CONSTRAINT UQ_User_Info_id UNIQUE;
GO

Auto increment syntax errors

4 Answers4