How to change primary key column length 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 PK, so I get the following error:

Msg 5074, Level 16, State 1, Line 1
The PK_TableName object depends on the Personal Identifier column.

PersonID = PK.

I read What is sql to change the field length of a column of a table in a sql server that applies only to columns other than PK.

I tried this:

ALTER TABLE table_name
ALTER COLUMN column_name <new datatype>

Answer 1

See an example example of how to increase the size of a primary column.

- create a sample table

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

- constratint

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

-

ALTER TABLE abc
DROP CONSTRAINT PK__abc__3213E83F74EAC69B

PK__abc__3213E83F74EAC69B ,

sys.key_constraints

- null

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

-

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>)

Answer 3

SQLServer 2008 did not allow me to change the primary key with the data, so I disabled all the restrictions, ran the command and activated all the restrictions again. Teams:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

-- commands here

EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"