Tag Archives: add foreign key

Add a Primary Key Constraint to an Existing Table

Here are some handy snippets for how to create a primary key on an existing table.

To add an identity column on the fly and then make it a primary key …

ALTER TABLE dbo.Table1 ADD
-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY;

To add a primary key to an existing column that is not null – first set the nullable column to NOT NULL …

ALTER TABLE dbo.Table1
ALTER COLUMN column_b int NOT NULL; 

Now to add a primary key to an existing column that is not null …

ALTER TABLE dbo.Table1 WITH NOCHECK
ADD CONSTRAINT column_b_pk PRIMARY KEY CLUSTERED (column_b)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);

Now you have a primary key on this table it is easy to create a Foreign Key Constraint against it.

ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD CONSTRAINT [FK_column_b] FOREIGN KEY(column_b)
REFERENCES [dbo].[Table1] (column_b)

Reference MSDN.