how to alter column in sql

Praveena K 21 Reputation points
2022-11-28T15:13:43.623+00:00

Hi All ,

i am trying to alter the column and the column name is valid can someone help me with this

ALTER TABLE categories
ALTER CategoryID INT NOT null AUTO_INCREMENT, AUTO_INCREMENT=9;

Error - uploaded 264882-error.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,310 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Jingyang Li 5,891 Reputation points
    2022-11-28T17:21:38.793+00:00
     ALTER TABLE categories  
     Drop column CategoryID;  
      
    ALTER TABLE categories  
     Add  CategoryID INT identity (1,9);  
    
    2 people found this answer helpful.
    0 comments No comments

  2. Olaf Helper 43,161 Reputation points
    2022-11-28T15:26:52.267+00:00

    If you want to alter an existing column, then then keyword COLUMN is mandator =>

    ALTER TABLE categories  
    ALTER COLUMN CategoryID INT NOT null  ...  
    

    AUTO_INCREMENT is no valid T-SQL keyword.
    See https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16 for correct syntax.


  3. NikoXu-msft 1,911 Reputation points
    2022-11-29T02:33:36.85+00:00

    Hi @Praveena K ,

    AUTO_INCREMENT is not a reasonable syntax for t-sql, there are alternatives (identity) in t-sql. However, you cannot alter a column to be an IDENTITY column.
    What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name. Just like JingyangLi said.

    Best regards,
    Niko

    ----------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments