0

I have a table called EMPLOYEE_DETAILS with these columns:

    @EMPLOYEEE_ID,
    @EMPLOYEE_FIRSTNAME,
    @EMPLOYEE_MIDDEL_NAME,
    @EMPLOYEE_LAST_NAME,
    @EMPLOYEE_CODE,
    @EMPLOYEE_WORKDEPT,
    @EMPLOYEE_PHONENO,
    @EMPLOYEEE_HIREDATE,
    @EMPLOYEE_JOB,
    @EMPLOYEE_POSITION,
    @EMPLOYEE_SEX,
    @EMPLOYEE_BIRTHDATE,
    @EMPLOYEE_SALARY,
    @EMPLOYEE_BONUS,
    @EMPLOYEE_COMM

Now, EMPLOYEE_ID is the primary key, but it does not have autoincrement value.

How to provide autoincrement value if the column has already created without deleting the column structure?

Below is the query that I have tried, but I got an error:

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'IDENTITY'.

Code: 

ALTER TABLE EMPLOYEE_DETAILS 
  ALTER COLUMN EMPLOYEE_ID IDENTITY(1,1);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amit Samnani
  • 1
  • 2

1 Answers1

-2

To modify the an already created column as autoincrement. Try this query:

 ALTER TABLE table_name MODIFY EMPLOYEE_ID datatype(length) AUTO_INCREMENT PRIMARY KEY

You cannot make an already existing column as an IDENTITY column. Either you drop and recreate the table with the column marked as IDENTITY', or drop the column and add a new IDENTITY` column.

Deep Lathia
  • 750
  • 7
  • 18