how to alter column in sql

Question

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

Answer 1

 ALTER TABLE categories  
 Drop column CategoryID;  
  
ALTER TABLE categories  
 Add  CategoryID INT identity (1,9);  

Answer 2

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.

Answer 3

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.