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.

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

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