SQL PRIMARY KEY CONSTRAINT Syntax

In this step by step SQL Tutorial I will show you how to create PRIMARY KEY Constraint. For the purpose of this tutorial I will use SQL Server 2012.

Other Versions of SQL Server:

• SQL Server 2012
  • This blog post has been written using SQL Server 2012
• SQL Server 2008 / R2
  • The examples we used should work in SQL Server 2008 and R2
• SQL Server 2005
  • We suspect that this will work in SQL Server 2005 as well

Who is it for?

• People who would like to learn basics of using PRIMARY KEY Constraint
• Those who are preparing for Microsoft certifications and need to cover this topic.
  • Exam Guide - 70-461 Querying SQL Server 2012

Contents

• ALTER TABLE ADD PRIMARY KEY Constraint Introduction
• ALTER TABLE ADD PRIMARY KEY Constraint Example

ALTER TABLE ADD PRIMARY KEY Constraint Introduction

PRIMARY KEY  uniquely identifies each record in a database table.

Primary Key is a constraint as it does not allow duplicated records. Using ALTER TABLE ADD CONSTRAINT PRIMARY KEY statement we are able to create primary key and give it a proper name. Name of Primary Key Constraint is important if you need comply with company standards and would like to keep the code more manageable which is particularly important with bigger solutions. You can also use SQL CREATE TABLE to add Primary Key but we don't recommend it and we also suggest to use Visual Studio Database Project.

NOTE: You can have only one Primary Key in a table. If you need to enforce uniqueness of fields multiple times than check our SQL unique key constraint article.

PRIMARY KEY Constraint Syntax

Below you can syntax that allows you to create 

ALTER TABLE table name 
ADD CONSTRAINT pk_Name PRIMARY KEY (ID)

Important: We received a comment from Tony and decided to make a note here. In the above syntax we add a constraint and use Primary Key (ID) that means that our constraint is actually Primary Key that we are creating right now. Remember Constraint can be on a field or a number of fields (CHECK, NULL, DEFAULT) but we also have row/table based constraints like Primary Key, Foreign Key, Unique Key all of them are constraints and one way of creating them (best practice, also makes sense if you use Visual Studio Database Project).

In the Object Explorer I open required database in this case it is AdventureWorksDW2012 then I expand Tables folder and find table where I will use ALTER TABLE ADD CONSTRAINT PRIMARY KEY example. In this case it is a table I created and is called dbo.Client.

NOTE: I have created my own table because there are already Primary Keys.

ALTER TABLE ADD PRIMARY KEY Constraint Example

I use Client table and you can see that my Keys folder is empty.

Below you can see my code and you that I ALTER TABLE dbo.Client and ADD CONSTRAINT with name pk_ClientID and specify constraint type which in my case is PRIMARY KEY and specify columns. In my case I specified one column which is often the case but in some cases you might need to specify more than one column and you can do that by separating the field names using comma.

In the Messages box I see information that my query was run successfully. 

I use refresh button in the Object Explorer and in my Keys folder I can see pk_ClientID constraint.

I hope that will helps

Katie