Need help in updating primary key field

Question

create table school_student(
    id int not null,
    name varchar(20),
    DOB Date,
    Address varchar(50),
    phone_no int,
    primary key(id)
);

need to update the primary key column to auto increment in sql

alter table school_student alter column  id int not null identity(5,1) primary key

i am getting below error,

Incorrect syntax near the keyword 'identity'.

Already inserted 4 records in that table , after that i am trying to set the auto increment key here so used identity(5,1) — Ammu Samu, Aug 25 '22 at 17:37

score 0 · Answer 1 · edited Aug 31 '22 at 23:48

Thanks Mayur Sawant for your reply from other post :) Its helped to drop the primary and foreign key .

Reference to his post : Usage of Alter command to drop Primary key and Foreign Key

First of all you need to find the constraint name related to the Primary and Foreign keys. Then you need to use these constraint names while dropping the keys.

select *
from
information_schema.key_column_usage
where
table_name = 'my_table'
From the above query you will be able to find the constraint names.

Now use the below syntax to drop the keys.

ALTER TABLE tablename DROP CONSTRAINT constraint name

Need help in updating primary key field

1 Answers1