How to change the column length of a primary key in SQL Server?

Question

I know how to change the length of a column, but my SQL statement fails because the column I'm trying to change is a PK, so I get the following error:

Msg 5074, Level 16, State 1, Line 1
The object 'PK_TableName' is dependent on column 'PersonID'.

PersonID = PK.

I've read What is the sql to change the field length of a table column in sql server which only applies to non-PK columns.

I tried this:

ALTER TABLE table_name
ALTER COLUMN column_name <new datatype>

Answer 1

See below sample example how to increase size of the primary column

1. Create a sample table

   create table abc (id varchar(10) primary key)

2. Find primary constraint in key constraints tables

   select object_name(object_id),* from sys.key_constraints where object_name(parent_object_id) = 'abc

3. Drop constraint

   ALTER TABLE abc DROP CONSTRAINT PK__abc__3213E83F74EAC69B

   (Replace PK__abc__3213E83F74EAC69B with constraint name you receive.)

4. Add not null

   ALTER TABLE abc alter column id varchar(20) NOT NULL;

5. Add primary key again

   ALTER TABLE abc ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (id)

Answer 2

ALTER TABLE <Table_Name>
DROP CONSTRAINT <constraint_name>

ALTER TABLE table_name
ALTER COLUMN column_name datatype

ALTER TABLE <Table_Name>
ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)