«

»

Oct 27

how to alter a column data type which is a primary key in the table

Hi, In this article I will explain you how to alter a column data type which is a primary key in the table

–>We can’t alter the primary key column data type directly by using query

–>To do that we have to delete the primary key first and then we have to alter the column data type and then we have to set primary key

–>Here i am changing data type “Int” to “bigint”

I will show you how to do that with an example

Before EmpId column data type modification image

Alter

–>Tbl_Mst_EmployeeDetails is a Table Name

–>I am changing the EmpID column datatype

–This Query will drop the primary key temporarily

[code language=”sql”]

ALTER TABLE Tbl_Mst_EmployeeDetails

drop CONSTRAINT PK_Tbl_Mst_EmployeeDetails

[/code]

— This Query will change data type

[code language=”sql”]

ALTER TABLE Tbl_Mst_EmployeeDetails

ALTER COLUMN EmpID BigInt not null

[/code]

— This Query will add primary key

[code language=”sql”]

ALTER TABLE Tbl_Mst_EmployeeDetails

ADD CONSTRAINT PK_Tbl_Mst_EmployeeDetails PRIMARY KEY (EmpID)

[/code]

o/p:After EmpId column data type modified image

Alter2

Happy Coding!!!!

Admin.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>