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

by

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

More Related Contents:

  1. SQL Server 2005 ROW_NUMBER() without ORDER BY
  2. SQL server identity column values start at 0 instead of 1
  3. Convert text to system date format [duplicate]
  4. How to get a particular preceding value of a column in SQL [closed]
  5. How to select just the row in the second table?
  6. How do I obtain a Query Execution Plan in SQL Server?
  7. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  8. Auto Increment after delete in MySQL
  9. sql query to return differences between two tables
  10. Generate SQL Create Scripts for existing tables with Query
  11. SQL Server 2005 How Create a Unique Constraint?
  12. SQL Server: Make all UPPER case to Proper Case/Title Case
  13. How do I delete from multiple tables using INNER JOIN in SQL server
  14. Does Foreign Key improve query performance?
  15. Export SQL query data to Excel
  16. What is a ‘multi-part identifier’ and why can’t it be bound?
  17. sql server select first row from a group
  18. CASE WHEN statement for ORDER BY clause
  19. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  20. CTE error: “Types don’t match between the anchor and the recursive part”
  21. Automate Version number Retrieval from .Dtsx files
  22. How do I remove extended ASCII characters from a string in T-SQL?
  23. The text, ntext, and image data > types cannot be compared or sorted, except when using IS NULL or LIKE > operator
  24. How can I see all the “special” characters permissible in a varchar or char field in SQL Server? [closed]
  25. How to correctly insert newline in nvarchar [duplicate]
  26. How do you copy a record in a SQL table but swap out the unique id of the new row?
  27. Using IF ELSE statement based on Count to execute different Insert statements
  28. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  29. SQL SERVER DATETIME FORMAT
  30. Insert all values of a table into another table in SQL

Leave a Comment