Saturday, 28 July 2012

PRIMARY KEY Constraint


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)

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
Post a Comment