Adding a column to the primary key.

13

November 13, 2014 by Kenneth Fisher

Every now and again you realize that the primary key for a table does not uniquely describe a row. Not really a big deal right? Just add an additional column in order to make it unique and move on. Easy enough if the column already exists and does not allow NULLs. However what do you do if you need to add a brand new column? Or if the column already exists but allows NULLs?

Not to worry! While it’s a little bit more complicated it’s certainly not difficult.

Possibility one the column already exists:

-- Setup code
CREATE TABLE PrimaryKeyColumnAdd (
	Column1 int NOT NULL,
	Column2 int NULL,
	CONSTRAINT pk_PrimaryKeyColumnAdd PRIMARY KEY (Column1)
	);
GO
INSERT INTO PrimaryKeyColumnAdd VALUES
	(1,NULL), (2,1), (3,4), (4,2);
GO

Step one is to change Column2 to be NOT NULL. In order to do that you have to make sure there are no rows with NULL values. And that of course means deciding on a default value (or filling in with the correct values if there is no default). Generally that ends up being 0 for numbers, ” for strings and various default dates (01/01/1900, 12/31/9999, etc) for date columns, but of course it doesn’t have to be.

UPDATE PrimaryKeyColumnAdd SET Column2 = 0
WHERE Column2 IS NULL;

Next modify the column.

ALTER TABLE PrimaryKeyColumnAdd ALTER COLUMN Column2 int NOT NULL;

And step two update the primary key. Don’t forget that the primary key is a constraint and constraints cannot be altered only dropped and re-created.

ALTER TABLE PrimaryKeyColumnAdd DROP CONSTRAINT pk_PrimaryKeyColumnAdd;

ALTER TABLE PrimaryKeyColumnAdd ADD CONSTRAINT pk_PrimaryKeyColumnAdd 
	PRIMARY KEY (Column1, Column2);

 
 
Possibility two we have to add a new column:

The first step is to add the new column. Remember that the column cannot allow NULLs in order to use it for the primary key so we have one of two options.

  • Add the column where it will allow NULLs, update the rows with a value, and modify the column to not allow NULLs.
    ALTER TABLE PrimaryKeyColumnAdd ADD Column3 int NULL;
     
    UPDATE PrimaryKeyColumnAdd SET Column3 = 0
    WHERE Column3 IS NULL;
    
    ALTER TABLE PrimaryKeyColumnAdd ALTER Column3 int NOT NULL;
  • Add the column NOT NULL with a default
    ALTER TABLE PrimaryKeyColumnAdd ADD Column3 int NOT NULL
    	CONSTRAINT df_Column3 DEFAULT (0);

And now we again change the primary key by dropping it and re-creating it.

ALTER TABLE PrimaryKeyColumnAdd DROP CONSTRAINT pk_PrimaryKeyColumnAdd;

ALTER TABLE PrimaryKeyColumnAdd ADD CONSTRAINT pk_PrimaryKeyColumnAdd 
	PRIMARY KEY (Column1, Column2, Column3);

Some people may be wondering why I’m posting something that seems so relatively simple. And to be fair it is pretty simple; once you know it. Remember that we don’t start out knowing everything, particularly with SQL (of any flavor). In fact I recently had a co-worker ask me about this very subject and he is by no means new to being a DBA.

13 thoughts on “Adding a column to the primary key.

  1. It will be more interesting if you provide example when another table refer to PrimaryKeyColumnAdd

    • Do you mean like a foreign key? If so it has to be dropped before the changes then re-created. Also if you are changing your primary key it may or may not match the columns available in the other table anymore.

      You can of course link a foreign key up to a unique key but if your original primary key can be used as a unique key then there really isn’t much point in changing it. 🙂

  2. Unfortunately, dropping a clustered key (and primary keys are clustered by default) is not supported by SQL Azure, which makes it more complicated to modify PKs

  3. […] in terms of how things run but it does make it a bit of a pain when you need to code with it. Say dropping the PRIMARY KEY constraint before adding a column to it. So what causes names like this to show up in your object […]

  4. CDAlternative says:

    The PRIMARY KEY is an INDEX and an INDEX can be re-created in one pass without having to drop it first. This is possible if the index is extended with one or more columns because the current set of columns already is guaranteeing uniqueness.

    • Can you show me the syntax? To the best of my knowledge this isn’t possible but if you can show me how I’d LOVE it. It would make my life so much easier at times. Both for modifying indexes and primary keys.

      • Stefan Boumans says:

        Hello,

        This was supposed to the job:

      • Stefan Boumans says:

        Hello,

        This technique can be used, however, to move table data to another filegroup, without having to drop the Primary Key and Foreign Key constraints first and then having to recreate them.

        http://stackoverflow.com/questions/11228039/sql-server-syntax-to-create-clustered-primary-key

        I guess that’s what I used it for, before (as well).

        Kind regards,

        Stefan Boumans

        From: Stefan Boumans
        Sent: Tuesday, July 14, 2015 3:33 PM
        To: ‘SQL Studies’
        Subject: RE: [New comment] Adding a column to the primary key.

        Hello,

        This was supposed to the job:

        • Ahh. Yes I probably should have included that syntax in the post. Technically though you should realize that you are dropping and re-creating the primary key behind the scenes. That’s what the DROP_EXISTING part of the syntax means.

  5. CDAlternative says:

    Yes, I know. But you can do it in a single statement and I do believe it also works faster, i.e., it’s not the same as _technically_ dropping and creating it again manually (and all of its dependencies).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: