.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Primary Key in SQL Server

Posted By:Syed Shakeer Hussain       Posted Date: August 21, 2009    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  

In this Article you can learn how to create and what are the Rules for  Primary Key in SQL Server.

1)A primary key column cannot contain NULL values.
2)Primary key must contain unique values.

3)Each table  can have only one primary key.

How to create a Primay Key in a Table:

For Example creating a table with a name as 'student_details' and
having 2 columns as 'id','name'.Giving the datatype for 'id' column as and
 'name' column as varchar.
Open your SqlServer 2000/2005/2008 'sql EnterPrise Manager'.
Select  your database.In you selected database Right click on Tables
and select on 'NewTable' as shown in below picture.

A NewTable window will open containing Column name and Datatype.Enter column name as id and name.You can keep you own datatype for you column by giving click in data type column  as shown in Below picture.

Now  save your table by giving tablename as 'student_details'.
Now our student_details table structure is ready.

How to create a primary key to an existing table:-


ALTER TABLE student_details ADD PRIMARY KEY (id);

here 'id' is a column name.

1)Explanation for 'A primary key column cannot contain NULL values':

Run the Above query in SqlQuery Analyzer and see what msg it will gives in the picture.

It gives the Error Message as 'Cannot define PRIMARY KEY constraint on nullable column in table 'student_details' '.That means a primary key column cannot contain NULL values.
you can see in the below picture,student_details table 'id' column contains null values.

so for creating a Primary key column make a id column as NotNull(unchecked) as shown in below picture.

Now Run your above SqlQuery in sqlquery analyzer.It gives Message as
'The command(s) completed successfully'.Now your Priamay Key column is created sucessfully.

2)Explanation for  'Primary key must contain unique values':-

Enterind some values in SqlQuery analyzer as below and Run it:

It gives the Error Msg as 'Cannot insert duplicate key in object 'student_details'.
That means in 'id' column same number enterd more than one time.you can identified that i had enterd for a primary key column 'id' as 100 for Smith and Kelvin.
So you can Understand here with above example that no two values with same values cannot enterd repeatedly.

3)Explanation for 'Each table can have only one primary key':-

Here i created one more column as 'Rollno' datatype as numeric to
understand for this explanation.
(here i deleted all values and enterd again some values for 'id','name' and 'rollno' columns)
Creating another priamrykey for a 'rollno' column by using below syntax:

ALTER TABLE student_details ADD PRIMARY KEY (rollno);


above 'rollno' is a column name.

Run in Sql Query Analyzer as follows:

It gives error Message as Table 'student_details' already has a primary key defined on it.That means we had already created a Primarykey,No more Primary keys are not allowed in a table.

Thanks for reading my article!

Syed Shakeer Hussain

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend