Thursday 11 October 2012

Constraints sql server


     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

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
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) REFERENCESUserDetails(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

1 comment:

  1. Constraints: Rules defined on the table which cannot be violated by the users.
    Types:
    Domain Integerity Constraints:
    Check: Limits the range of values or domain, checks the values which a col can have.
    Referntial Integrity Constraints:
    Foreign Key: Referntial integrity b/w 2 tables, one table col values based on other table col values.
    Entity Integrity Constraints:
    Default Constraint: defines the default value for a column.
    Null: allows null value.
    NOT null:Donot allow null value.
    Primary Key: Ensure no duplicate data and not allow nulls.
    Unique Key: donot allow duplicate values, but allows one null per col.Can have multiple unique constraints on a table

    ReplyDelete