Tag: SQLNewBlogger

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.

I had written about a FK in a CREATE TABLE statement recently, but the second half of this was that after the original question, the person asked if this would also work for a self-referencing FK. It does, and I wrote this to show that.

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

Creating the FK

The last post showed how to create the FK, but this works within a table as well. Let’s say I want to have an Employee table that links back one employee to another, who is their manager. That type of structure looks like this:

CREATE TABLE [dbo].[Employee](
     [EmpID] [INT] NOT NULL,
     [EmpName] [VARCHAR](20) NULL,
     [MgrID] [INT] NULL,
  CONSTRAINT [EmployeePK] PRIMARY KEY CLUSTERED 
(
     [EmpID] ASC
)
) ON [PRIMARY]
GO

I can add a link that makes MgrID a FK reference by altering the code like this:

CREATE TABLE [dbo].[Employee](
     [EmpID] [INT] NOT NULL,
     [EmpName] [VARCHAR](20) NULL,
     [MgrID] [INT] NULL,
  CONSTRAINT [EmployeePK] PRIMARY KEY CLUSTERED 
(
     [EmpID] ASC
),
CONSTRAINT FK_MgrID_EmpID FOREIGN KEY (MgrID) REFERENCES dbo.Employee (EmpID)
) 
GO

Easy.

SQL New Blogger

This is a post that took me less than 10 minutes to write. I changed the code from the previous post and wrote this right after the other one. The search and replace was the longest code part, and then the writing was quick, 5 minutes.

This is a core skill for a DBA or developer. Write your own post to show how and why to build a self referencing FK for some scenario that you work with in your job, or in a project.

I had someone ask this question recently and had to double check the syntax myself, so I thought this would make a nice SQL New Blogger post.

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

Defining a Foreign Key

Most people define a foreign key like this:

ALTER TABLE [dbo].[OrderLine]  WITH CHECK ADD  CONSTRAINT [FK_OrderLine_Order] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Order] ([OrderID])
GO

This assumes I’ve added a table called dbo.Order with a PK of OrderID.

However, I can do this in the CREATE TABLE statement, like shown below. I add a new section after a column with the CONSTRAINT keyword. Then I name the constraint, which is always a good practice. I can then add the FK keyword, the column and the references that connects this child column to the parent column.

CREATE TABLE dbo.OrderLine
( OrderLineID INT NOT NULL CONSTRAINT OrderLinePK PRIMARY KEY
, OrderID INT
, Qty INT
, Price NUMERIC(10,2)
, CONSTRAINT FK_OrderLine_Order FOREIGN KEY (OrderID) REFERENCES dbo.[Order](OrderID)
)
GO

Easy to do and this keeps my code clean.

Note that if I script this out in SSMS, I’ll get this:

CREATE TABLE [dbo].[OrderLine](
[OrderLineID] [int] NOT NULL,
[OrderID] [int] NULL,
[Qty] [int] NULL,
[Price] [numeric](10, 2) NULL,
CONSTRAINT [OrderLinePK] PRIMARY KEY CLUSTERED
(
[OrderLineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrderLine]  WITH CHECK ADD  CONSTRAINT [FK_OrderLine_Order] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Order] ([OrderID])
GO

Nothing wrong with that, but knowing both syntaxes is a good idea. Plus, if you know this is a child column, define it right away.

SQL New Blogger

This is a post that took me about 15 minutes to write. I had to create and drop the tables a few times and verify I had the syntax correct, and then explain and format things.

This is a core skill for a DBA or developer. You ought to know how to define a FK and use them where appropriate. Write your own post to show how to build a FK for some scenario that you work with in your job, or in a project.