Sql-server – How to change VARCHAR length in PRIMARY KEY in SQL Server 2014

alter-tableprimary-keysql serversql server 2014varchar

I need to change the VARCHAR length from 20 to 25 in a code column in a library table in SQL Server 2014, but SSMS wants to DROP and RE-CREATE all my tables where the library table in question is linked to.

Is there a way to change the VARCHAR length of a PRIMARY KEY without dropping all the tables?

Best Answer

As you are increasing the length you can just do an alter column, for example:

CREATE TABLE dbo.T1
    (
      C1 VARCHAR(20) NOT NULL
                     CONSTRAINT PK_T1 PRIMARY KEY ,
      C2 INT
    );
GO

INSERT  INTO dbo.T1
VALUES  ( 'test', 1 ),
        ( 'test2', 2 );
GO

ALTER TABLE dbo.T1 ALTER COLUMN C1 VARCHAR(25) NOT NULL;

Further reading on the ALTER TABLE syntax at https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql

Related Question