How do I add a auto increment primary key in SQL Server database

Question

I have a table set up that currently has no primary key  All I need to do is add a primary key  no null  auto increment   I m working with a Microsoft SQL Server database  I understand that it can t be done in a single command but every command I try keeps returning syntax errors   edit ---------------  I have created the primary key and even set it as not null  However  I can t set up the auto increment   I ve tried   ALTER TABLE tableName MODIFY id NVARCHAR 20  auto increment ALTER TABLE tableName ALTER COLUMN id NVARCHAR 20  auto increment ALTER TABLE tableName MODIFY id NVARCHAR 20  auto increment ALTER TABLE tableName ALTER COLUMN id NVARCHAR 20  auto increment   I m using NVARCHAR because it wouldn t let me set NOT NULL under int

User · Answer

you can try this    ALTER TABLE Your Table  ADD table ID int NOT NULL PRIMARY KEY auto increment

User · Answer

If the table already contains data and you want to change one of the columns to identity   First create a new table that has the same columns and specify the primary key-kolumn   create table TempTable       Id int not null identity 1  1  primary key     --  Other columns        Then copy all rows from the original table to the new table using a standard insert-statement   Then drop the original table   And finally rename TempTable to whatever you want using sp rename   http   msdn microsoft com en-us library ms188351 aspx

User · Answer

It can be done in a single command  You need to set the IDENTITY property for  auto number    ALTER TABLE MyTable ADD mytableID int NOT NULL IDENTITY  1 1  PRIMARY KEY   More precisely  to set a named table level constraint   ALTER TABLE MyTable    ADD MytableID int NOT NULL IDENTITY  1 1      CONSTRAINT PK MyTable PRIMARY KEY CLUSTERED  MyTableID    See ALTER TABLE and IDENTITY on MSDN

User · Answer

You can also perform this action via SQL Server Management Studio   Right click on your selected table - gt  Modify Right click on the field you want to set as PK -- gt  Set Primary Key Under Column Properties set  quot Identity Specification quot  to Yes  then specify the starting value and increment value   Then in the future if you want to be able to just script this kind of thing out you can right click on the table you just modified and select   quot SCRIPT TABLE AS quot  -- gt  CREATE TO  so that you can see for yourself the correct syntax to perform this action

User · Answer

If you have the column it s very easy   Using the designer  you could set the column as an identity  1 1   right click on the table   design   in part left  right click    properties   in identity columns  select  column     Properties      Identity column

User · Answer

In SQL Server 2008    Right click on table Go to design Select numeric datatype Add Name to the new column Make Identity Specification to  YES

[sql] How do I add a auto_increment primary key in SQL Server database?

Examples related to sql

Examples related to sql-server

Examples related to auto-increment