How to alter the data type of primary key in a table of ms sql server database

In this tutorial we will learn how to alter the data type of primary key in a table of ms sql server database. We should think widely when we create any table in database. We should think how much data can be stored in the columns of the table and in which format the data will be stored. Many people intentionally set the data type of primary key to int while on the same time they know very well that a time will come when data length will exceed the limitations of int and at that time they have to alter the primary key data type from int to bigint. This tutorial is for those people

Steps to alter the data type of column set as a primary key in a table of ms sql server database

Step 1:- Drop Constraint

ALTER TABLE student
DROP CONSTRAINT PK_student

Step 2:- Alter column data type

ALTER TABLE student
alter column student_id bigint NOT NULL

student_id column was prevously set as primary key and will become primary key again with alter data type


Step 3:- ADD CONSTRAINT

ALTER TABLE student
ADD CONSTRAINT PK_student PRIMARY KEY (student_id)

I hope it will be great helpful for you.