Powered by Blogger.

Saturday, February 15, 2014

Constraints in SQL Server 2008



In this article, we will discuss about different Constraints in SQL server.The Constraints are like Not Null, Unique, Primary key, Check, Default, Foreign Key. Also you can check my previous posts on:

- Query to get records between two dates in sql server 2008

- Serialization and Deserialization in Remoting in C#.Net

- Anonymous Types in C#.Net

Constraints are used to enforce the integrity of the data in the columns, SQL Server 2005 provides the following mechanism to enforce the data in the column.

-Not Null
-Unique
-Primary Key
-Check
-Default
-Foreign Key

Not Null:
If it is applied to a column that column will not allow null values into it. This canbe imposed on any no of columns.

Syntax:
CREATE TABLE <table_name>(column_name1 <dtype> [width] [Not Null],column_name1 <dtype> [width] [Not Null],
                        ..........................................
                        ..........................................
                        column_namen <dtype> [width] [Not Null])

For Example:
CREATE TABLE Bank(Custid int Not Null,Cname varchar(50),Bal decimal(7,2) Not Null)
After creating if you try to insert a null value into the Custid or Bal Columns it will restrict us.

Drawback:
The drawback with Not Null is that even if it restricts null values it will not restrict dupulicate values.

Unique:
To avoid the drawbacks of Not Null, we are using Unique constraints.
If it is imposed on a column or columns they will not allow duplicates or duplicate values into it.

Remember one thing: Unique, Primary key, Check, Foreign key Constraints can be imposed in two ways
-Column Level Defination
-Table Level Defination

Column Level Defination: 
In this case the constraints defination is immediately followed after the Column defination

Syntax:
Create table <table_name >(column)_name1 <dtype> [width] [[Constraint<Name>] <Type>],
                          column)_name1 <dtype> [width] [[Constraint<Name>] <Type>],
                          .........................................................
                          column)_name1 <dtype> [width] [[Constraint<Name>] <Type>],

Example
create table Bank(Custid int Unique,Cname varchar(50),Bal decimal(7,2)Not Null)

Now if you will try to insert a duplicate value into the Custid column it will restrict us.

Table Level Defination: 
In this case the constraint defination is immediately followed after the column defination.

CREATE TABLE <table_name>(column_name1 <dtype> [width],column_name1 <dtype> [width],
                        ..........................................
                        ..........................................
                        column_namen <dtype> [width],
                        [[Constraint<Name>] <type> (<collist>)],
                        .......................................)

Example:
Create table Bank(Custid int,Cname varchar(50),Bal decimal(7,2) Not Null,Constraint Cusid_UQ Unique(Custid))
Primary Key Constraint:While creating a primarykey constraint we need to keep in the mind that a table can contain only one primary key which can be present in on a single column or multiple column also.

Creating Primary key in Column Level:Create table Bank(Custid int Constraint Cusid_PK Primary Key,Cname varchar(50),Bal decimal(7,2) Not Null)

Creating Primary key in table level:create table Bank(Custid int,Cname varchar(50), Bal decimal(7,2) Not Null, Constraint Custid_PK Primary Key(Custid))

Check Constraints:
If we want to check the values present in a column to be according to a specified value we use this costraint.

For Example
Creating check constraint in column level:
Create table Bank(Custid int,Cname varchar(50), Bal decimal(7,2) Not Null, Constraint Bal_CK Check(Bal>=1000))

Creating check constraint in table level:
Create table Bank(Custid int,Cname varchar(50), Bal decimal(7,2) Not Null, Constraint Bal_CK Check(Bal BETWEEN 1000 AND 5000))

Foreign Key Constraint:
It is a column or combination of columns that is used to establish and enforce a link between the data in two tables.
In a foreign key key reference, a link is created between two tables when the columns in a table reference the columns that hold the primary key of other table, which becomes a foreign key in the first table.

Creating the Foreign key constraints in column level:
Create table Emp (Empno int, Ename varchar(100), Job varchar(100), Mgr int, HireDate datetime, sal money, Comm money, Deptno int Constraint Deptno_Ref References Dept(Deptno))

Creating the Foreign key constraints in table level:
Create table Emp (Empno int, Ename varchar(100), Job varchar(100), Mgr int, HireDate datetime, sal money, Comm money, Deptno int Constraint Deptno_RefForeign Key(Deptno) References Dept(Deptno))



0 comments

Post a Comment