Change Column ID to Auto Increment in SQL Server

sql serversql-server-2012

I started to use SQL Server recently and I still don't know the best way to do some things.

I created all the tables with a column ID as the primary key. Now when I try to insert values I get the following error:

"Cannot insert the value NULL into column 'id', table 'Project.dbo.Table'; column does not allow nulls. INSERT fails. The statement has been terminated."

What is a good, simple solution to this problem?

Best Answer

If your table is empty, you can drop and recreate the table again and add IDENTITY(1,1) to the column definition of id to make your id column in Project.dbo.Table auto increment.

Something like this.

CREATE TABLE Project.dbo.Table
(
id int IDENTITY(1,1) NOT NULL,
col2 VARCHAR(50) NULL,
...
...
);

You can either add PRIMARY KEY with the column name like this id int IDENTITY(1,1) NOT NULL PRIMARY KEY, or at the end after defining all columns like this CONSTRAINT PK_Table PRIMARY KEY(id)

SQL Server does not allow you to add IDENTITY by altering a table. use GO between IF EXISTS ...DROP TABLE and the CREATE TABLE statements to run those statements in separate batches.

Related Question