Voice of the DBA

A client asked this question recently: How do I change my numeric PK to a character type?

I decided to write a short blog on how to do this. This is the happy path, and not intended to cover all situations. I’ll write about some exceptions in a separate post.

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

The Scenario

A customer had a table where the PK was a number and wanted to change this to a character field. Here’s an example table with some data.

CREATE TABLE Invoice
( InvoiceID   INT NOT NULL CONSTRAINT InvoicePK PRIMARY KEY
, InvoiceDate DATE
, CustomerID  INT);
GO

INSERT dbo.Invoice
   (InvoiceID, InvoiceDate, CustomerID)
VALUES
   (1, '20230102', 3)
, (2, '20230103', 3)
, (3, '20230105', 4)
, (4, '20230106', 8)
, (5, '20230108', 11)
, (6, '20230109', 37);
GO

Now, the situation was really that the customer was generating numbers for documents, but they realized their business had changed and they needed to add characters to the data.

The Problem

There are two things to think about here. First, what happens with the data? In this case, converting an integer to a character is easy and works. As long as the character field is long enough, this works fine. We first want to be aware of the data loss potential, though SQL Server won’t allow this.

Second, we can’t change the data type because the PK is a constraint. If we try to change this type, we get an error:

We really need to remove this constraint.

If we have an outage window, this isn’t hard. If we don’t, then we have to be careful. In this case, I’ll assume we can pause the system and can make the changes without data changing in the table.

The Solution

The process to change the type is a few steps. I’ve shown them here.

1. remove the PK constraint
2. change the column
3. add the PK constraint back

This code will do this. I run three statements to make the change, wrapped in a transaction, with error handling to rollback if one fails

BEGIN TRAN
DECLARE @e INT = 0
ALTER TABLE dbo.Invoice DROP CONSTRAINT InvoicePK
IF @@ERROR<> 0 
  SELECT @e = 1
ALTER TABLE dbo.Invoice ALTER COLUMN InvoiceID VARCHAR(20) NOT NULL
IF @@ERROR<> 0 
  SELECT @e = 1
ALTER TABLE dbo.Invoice ADD CONSTRAINT InvoicePK PRIMARY KEY (InvoiceID)
IF @@ERROR<> 0 
  SELECT @e = 1
IF @e = 0
     COMMIT
ELSE 
     ROLLBACK

This will change the data type and then reset the PK, as you can see below. With all my data intact.

This is a simple scenario, and there are more considerations, but those are for another post.

SQL New Blogger

This post took me about 15 minutes to write. I took something I’d mocked up as a test for a client and then added that to this post. The code was barely changed, and I really renamed something and removed a few columns. Adding the text around this took most of the time.

This is something that all of you could do to show that you have this skill. Changing a PK isn’t something you want to do, and it is unusual, but it does happen at times. I’ve had this happen before, and there are various other exceptions. Note I’ve added a note at the bottom to link this in to a series looking at other changes. What if the data isn’t compatible? what if the type is too short? What decisions would you make about the new PK, as int to bigint is easy, but what about char to date and possible collisions? What about identity values?

You can do this and easily build 3-4 posts on this topic. Showcase your knowledge and you might create (and control) a fun discussion in an interview.