Lengthen a Primary Key–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw a post recently where someone needed to increase the size of a PK and was getting a table rebuild message in SSMS. This is short post to show that isn’t required.

First, let’s create a table and give it some data. Note that the PK is set to a specific size.

CREATE TABLE dbo.Document
(DocumentKey NVARCHAR(5) NOT NULL CONSTRAINT DocumentPK PRIMARY KEY
, DocumentName NVARCHAR(200)
, DocumentDate DATETIME2
)
GO
INSERT dbo.Document
     (
         DocumentKey
       , DocumentName
       , DocumentDate
     )
VALUES
     (N'ABC23', N'Something very interesting', '2019-01-02'),
     (N'QNI43', N'An adventure you admire', '2019-02-02'),
     (N'MNT33', N'Magnets describing life', '2019-03-04'),
     (N'DEF25', N'Time for nothing', '2019-03-12'),
     (N'HIJ54', N'Dreams of the dark', '2019-04-17')
GO
SELECT top 10
  *
  FROM dbo.Document AS d
GO

If I try to insert data that’s larger, I’ll get this message:

In SQL 2019, I’ll get a better error, but for now, this shows me a limitation of my key.

Now I’ll increase the size of the key. I use the ALTER TABLE … ALTER COLUMN statement.

ALTER TABLE dbo.Document ALTER COLUMN DocumentKey NVARCHAR(7) NOT NULL
GO

Now, I’ll run my failed insert again:

As you can see, I can increase the size of the PK without rebuilding the table. Making it smaller is a post for another day.

SQLNewBlogger

This was a quick repro I set up to answer the question for myself and others. I thought I could do this and spent five minutes proving it.

The longest part of this post was the test data. You could do the same thing, maybe showing how this relates to a child table as well. In fact, start today and you might beat me to creating that post.