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

sql sql-server primary-key

32,086

Solution 1

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

Create a sample table

create table abc (id varchar(10) primary key)
Find primary constraint in key constraints tables

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

ALTER TABLE abc DROP CONSTRAINT PK__abc__3213E83F74EAC69B

(Replace PK__abc__3213E83F74EAC69B with constraint name you receive.)
Add not null

ALTER TABLE abc alter column id varchar(20) NOT NULL;
Add primary key again

ALTER TABLE abc ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (id)

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

32,086

LearnByReading

Updated on July 15, 2020

Comments

LearnByReading almost 4 years
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>
```
- LearnByReading almost 9 years
  
  P.S. I do not want to drop/re-create the table because the table contains millions of rows...
- Jeremy almost 9 years
  
  I don't think you have a choice...
- NDJ almost 9 years
  
  you won't need to drop the table, but you will need to drop the pk constraint before changing the column (and potentially drop any referencing FK constraints) which you can recreate afterwards
- TTeeple almost 9 years
  
  Did you specify NOT NULL? Are you making the column bigger (going from varchar(6) to varchar(8)) or smaller (vice versa)? Check this post stackoverflow.com/questions/11118568/…
- Christian Barron almost 9 years
  
  I'm in agreement with NDJ, as far as I'm aware you will need to drop the PK constraint, modify and then reapply the constraint.
- LearnByReading almost 9 years
  
  @TTeeple , yes I did specify no null
LearnByReading almost 9 years

THanks for the reply. Not sure why my question was voted down. This way, this question will appear over and over again because negative questions do not show up in searches..
LearnByReading almost 9 years

THanks for the reply. Not sure why my question was voted down. This way, this question will appear over and over again because negative questions do not show up in searches..
Indra Prakash Tiwari almost 9 years

Hi @LearnByReading If this helped then accept the answer.