Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Primary key constraint in sql server, foreign key constraint, unique key constraint, not null, check constraint in SQL Server

Jul 10, 2011
Introduction:

Here I will explain primary key constraint in sql server, foreign key constraint in SQL sever, unique key constraint in sql server, not null constraint in sql server and check constraint in sql server.

Description:

A Constraint is a property that we can assign to column in table. By assigning constraint property on column we can prevent the users to enter inconsistent of data in columns. We can assign these Constraint properties during the time of creation of table (By Using CREATE TABLE statement) or during the time of changing the existing table structure (By Using ALTER TABLE statement).

In SQL we have different types of constraints are available those are

1.      Primary Key Constraint
2.      Unique Key Constraint
3.      Foreign Key Constraint
4.      Not Null Constraint
5.      Check Constraint

Now I will explain about each constraint clearly

SQL Primary Key Constraint:

Primary key constraint is used to uniquely identify each record in database table. It won’t allow repetition or duplication of data. Each table is having only one primary key constraint and it contains only unique values. Primary key constraint doesn’t accept null values.

Example of creating Primary Key constraint during the time of CREATE TABLE


Create Table SampleUserDetail
(
UserID integer PRIMARY KEY,
UserName varchar(50),
FirstName varchar(50),
LastName varchar(50)
)
Example of creating Primary Key constraint during the time of ALTER TABLE


ALTER TABLE SampleUserDetail ADD PRIMARY KEY (UserID)
To Drop Primary Key constraint on table use the below statement


ALTER TABLE SampleUserDetail DROP Constraint UserID

SQL Unique Key Constraint:

Unique key constraint is same as Primary key Constraint it doesn’t allow duplication or repetition of data in column and we can uniquely identify records in table. The main difference is Primary Key constraint won’t allow null values but unique key constraint allows null values. We have a chance to define only one primary key on table but we can define many unique key constraints on table.

Example of creating Unique Key constraint during the time of table creation


Create Table SampleUserDetail
(
UserID integer ,
UserName varchar(50),
FirstName varchar(50),
LastName varchar(50)
CONSTRAINT us_UserId UNIQUE (UserID)
)
Example of creating Unique Key constraint during the time of ALTER TABLE


ALTER TABLE SampleUserDetail ADD CONSTRAINT us_UserId UNIQUE (UserID)
To Drop Unique Key constraint on table use the below statement


ALTER TABLE SampleUserDetail DROP Constraint us_UserId

SQL Foreign Key Constraint:

A Foreign key in one table point to primary key in another table. The foreign key constraint is used to prevent the actions that would destroy the links between two tables.

Example of Foreign key constraint

Create one table with primary key and give name as UserDetails

UserID
UserName
FirstName
LastName
     1
SureshDasari
Suresh
Dasari
     2
PrasanthiDonthi
Prasanthi
Donthi
     3
MaheshDasari
Mahesh
Dasari
After create another table with Foreign Key and give name as SalaryDetails

SalID
Salary
UserID
     1
10000
1
     2
20000
2
     3
30000
3
The column “UserID” is a primary key in UserDetails table
The column “SalID” is a foreign key in SalaryDetails tables

If you observe above two tables UserID in “UserDetails” table points to UserID in “SalaryDetails”

Example of creating Foreign Key constraint during the time of table creation


Create Table SalaryDetails
(
SalaryID integer ,
Salary integer,
UserID varchar(50),
PRIMARY KEY (SalaryID),
CONSTRAINT fk_SalaryID FOREIGN KEY(UserID)
REFERENCES UserDetails(UserID)
)
Example of creating Foreign Key constraint during the time of ALTER TABLE


ALTER TABLE SalaryDetails ADD CONSTRAINT fk_SalaryID FOREIGN KEY (UserID) REFERENCES UserDetails(UserID)
To Drop Foreign Key constraint on table use the below statement


ALTER TABLE SampleUserDetail DROP Constraint fk_SalaryID

SQL Not NULL Constraint:

If we set Not Null constraint property on any column in table that column won’t accept NULL or Empty values. If you want enforce any column not to accept NULL or empty value just set Not NULL Constraint property for that particular column

Example of creating NOT NULL constraint during the time of table creation


Create Table SampleUserDetail
(
UserID integer NOT NULL,
UserName varchar(50) NOT NULL,
FirstName varchar(50),
LastName varchar(50)
)

SQL Check Constraint:

The check constraint is used to limit the value range that can be placed in a column. If we set Check constraint property on particular column the values in particular column must satisfy condition set by check constraint.   

Example of creating Check constraint during the time of CREATE TABLE


Create Table SampleUserDetail
(
UserID integer NOT NULL CHECK(UserID > 0),
UserName varchar(50),
FirstName varchar(50),
LastName varchar(50)
)
Example of creating Check constraint during the time of ALTER TABLE


ALTER TABLE SampleUserDetails ADD CONSTRAINT chk_UserID CHECK(UserID > 0)
To Drop Check constraint on table use the below statement


ALTER TABLE SampleUserDetails DROP CONSTRAINT chk_UserID

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

20 comments :

Sunil said...

Nice 1

Anonymous said...

thanks sir

Anonymous said...

gd post

Anonymous said...

this is the good site...

Anonymous said...

Goooood job

Prakash said...

Nice.........................

Surya said...

Easy to understand

mahadev gurav said...

good

Anonymous said...

thanks yaar!!!

bandita bahubalendra said...

Good one and easy to understand . Thanks!!!

dizüstü bilgisayar said...

good blogs thank you sharing

Neeraj said...

good its easy process to define all about
constraint ......

Anonymous said...

Easy to understand very helpful
Ankit Jain

Anonymous said...

nice one.....

Anonymous said...

Very Helpful...
Pls post more and more topic regarding C#...
Thanks Sir...

Anonymous said...

thnxs for help

Anonymous said...

Hello sir,
m usual follower of your block..
I learned lot from your blogs.. I really appreciate your efforts...
Sincerely, I would like to correct your mistake in above post...
While defining primary constraint you have not added the constraint keyword CONSTRAINT...
So,It gives error while dropping constraint as
...

'UserID' is not a constraint.
Could not drop constraint. See previous errors.
-------------------------------------------------
Example of creating Primary Key constraint during the time of CREATE TABLE

Create Table SampleUserDetail
(
UserID integer PRIMARY KEY,
UserName varchar(50),
FirstName varchar(50),
LastName varchar(50)
)

Anonymous said...

Serioulsy its too clear...:)
Thanks a lot for posting

Ravi said...

Toooo Clear , Tooo simple n Tooooo affective . I am sure whoever read your post will not forget ever about these constraints... Thanx again for sharing.:)

Dinesh said...

superb sir,i am a big fan of ur blog

Give your Valuable Comments

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.