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

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.

  • SQL Server 2005 Pivot on Unknown Number of Columns
  • What does exec sp_updatestats do?
  • SQL Server Count on a grouped by
  • SQL Syntax to Pivot multiple tables
  • ROW_NUMBER is not working in CTE
  • A not-existing column should not break the sql query within select
  • 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

    4 Solutions collect form web for “How do I add a auto_increment primary key in SQL Server database?”

    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),
       ADD CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (MyTableID)
    

    See ALTER TABLE and IDENTITY on MSDN

    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

    You can also perform this action via SQL Server Management Studio.

    Right click on your selected table -> Modify

    Right click on the field you want to set as PK –> Set Primary Key

    Under Column Properties set “Identity Specification” 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

    “SCRIPT TABLE AS” –> CREATE TO

    so that you can see for yourself the correct syntax to perform this action.

    If you have de column is very easy.

    by the designer you could set identity (1,1) right click on tbl => desing => in part left (right click) => properties => in identity columns select #column

    Properties
    /images//bNVwm.png

    idendtity column
    /images//wLt5n.png

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.