Adding a column to the primary key.
13November 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.
SQL Studies 
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. 🙂
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
I haven’t started working in Azure yet so I wasn’t aware of that. I did find this great answer on stackexchange that explains what can, and can’t be done, and how to do it. http://dba.stackexchange.com/questions/6937/how-can-i-alter-an-existing-primary-key-on-sql-azure
agreed, however, if you want to separate clustered index from primary key, you have only the option to re-create the table.
[…] 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 […]
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.
Hello,
This was supposed to the job:
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.
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).
You are absolutely right. It will update all of the dependencies behind the scenes but it’s certainly a good alternative.