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.

Filed under: Index, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: index, language sql, microsoft sql server, T-SQL, T-SQL Tuesday