sqlsql-serverprimary-key

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

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

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

    1. Find primary constraint in key constraints tables

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

    2. Drop constraint

      ALTER TABLE table_name DROP CONSTRAINT PK__abc__3213E83F74EAC69B

      (Replace PK__abc__3213E83F74EAC69B with constraint name you receive.)

    3. Change size from varchar(10) to varchar(20)

      ALTER TABLE table_name alter column column_name varchar(20) NOT NULL;

    4. Add primary key again

      ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column_name)