Constraints:-

These are used to apply restrictions on a table .

1)    Not null

2)    Unique

3)    Check

4)    Primary key

5)    Foreign key

(1)Not Null:- 

• A column Delclared with not null into that column null values are not allowed.
• Not null constraint will not accept nullvalues
• Sy:-Create table table Name(Column Name  DataType Not Null)

Ex:-

1. create table Emp1(empid int not null,Ename varchar(20))—creating table with not null constraint column
2. insert into Emp1 values(1,'Sita')—inserting a record
3. insert into emp1(EName) values('srinivas')—if  you try to execute the above statement

                 you will get the error message like

Cannot insert the value NULL into column 'empid', table 'Kumar.dbo.Emp1'; column does not allow nulls. INSERT fails.

The statement has been terminated.

(2)Unique:-

• Unique key constraint will not accept duplicate values(or ) Redundant values.
• Sy:- Create table table Name(Column Name  DataType unique)

Ex:-

1. create table emp2(Empid int unique,EName varchar(20))—creating a table with unique key constraint column
2. insert into emp2 values(1,'kalpana')—this record will be inserted into the Emp2 table
3. insert into emp2 values(1,'sravanthi')-this record will not be inserted into th Emp2 & it gives the error message like

Violation of UNIQUE KEY constraint 'UQ__emp2__AF2EBFA00425A276'. Cannot insert duplicate key in object 'dbo.emp2'.

The statement has been terminated.

(3)Check Constraint:- 

Check constraint validates data based on a condition.

It is used to apply a specific condition on a column /multiple columns in aTable.

Sy:- Create table table Name(Column Name  DataType Check(Condition)) 

Ex:-

1.  create table Emp3(Empid int check(empid>10),EName varchar(20)) 
2.  insert into Emp3 values(11,'padma')—this record will be inserted into table BCZ 11 is >1
3.  insert into Emp3 values(8,'sita')—if you try to insert this record it will give the error  message like

The INSERT statement conflicted with the CHECK constraint "CK__Emp3__Empid__07020F21". The conflict occurred in database "Kumar", table "dbo.Emp3", column 'Empid'.

The statement has been terminated.

(4)Primary Key:-

• A coloum declared with primary key doesnot allow duplicate values & null values.
• Sy:- -Create table table Name(Column Name  DataType primary key)

Ex:-

1. create table Emp4(EmpId int primary key,EName varchar(20))—creating a table with primary key coloumn
2. insert into Emp4 values (1,'lalitha')—this record will be inserted into the table Emp4
3. insert into Emp4 values (1,'lalitha')—if you try to insert this record then it will give the error message like

Violation of PRIMARY KEY constraint 'PK__Emp4__AF2DBB9909DE7BCC'. Cannot insert duplicate key in object 'dbo.Emp4'.

The statement has been terminated.

insert into Emp4(Ename) values ('Bharathi') --if you try to insert this record thenwill give the error message like

Cannot insert the value NULL into column 'EmpId',  table 'Kumar.dbo.Emp4'; column does not allow nulls. INSERT fails.The statement   has been terminated.

(5)Foreign Key:-

1.  A foreign key refers primary key (or )unique coloumns of another /same table.
2. It is used to establish relation between two tables
3. The value of foreign key should be match with primary key value
4. After declaring foreign key a relationship is established between two tables & that relation ship is called parent/child relationship.
5.  Table holding primary key is “Parent” and table holding foreign key is “child”(Refering) 

• Sy:-create table tableName(coloumnName datatype foreign key references primary key tablename(Primary key column Name))
• Ex:-
• create table Emp44(EId int foreign key references Emp4(EmpId),ESal Money)—this will creates a forein key 

• insert into Emp44 values(1,600000)—this record will be inserted into the Emp44 table insert into Emp44 values(2,160000)—if you try to insert this record then it will give    the error message like 

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Emp44__EId__0CBAE877". The conflict occurred in database "Kumar", table "dbo.Emp4", column 'EmpId'.

The statement has been terminated.

Because emp4 table , empid column does not contain the  value 2. 

Default:-

• It is used to assaign the default value for the coloumn .if user is not entering any value in that column it has to be assaign with default.
• Sy:-Create table tableName(ColoumnName datatype default(somenumber))

           Ex:-

1. Create table employee6(eno int default(1),Ename varachar(20))
2. Create table employee8(eno int ,ename varchar(20),doj datetime default getdate())
3. Insert into employee8 values(1,’samrat’,10-dec-2012)
4. Insert into employee8(eno,ename) values(3,’sampath’)

Identity:-

• It is used to apply the automatic generation numbers  for a perticular coloumn
• It is used to generate serial number for a column in a table mostly for a primary key
• Sy:-create table tableName(coloumn name datatype ideentity(seed(starting number),incr(increment by value))

• Both seed and incr are optional.
• If you don’t mention the seed ,incr then default seed,incr value is(1,1)
  Ex:-

1. Create table employee9(eno int identity(1000,1) primary key,ename varchar(20))