Sunday, March 8, 2015

Constraints in SQL Server

9:39 PM - By ajay desai 0

Constraints

Enforcing not null and unique constraint on columns of a table

         Not null

The not null constraint restricts null values to be entered for a column of a database table.
create table customer(custid int,cname varchar(50),balance decimal(9,2) not null,AOD date,status bit)
insert into customer values(101,'ramakrishna',null,'2014-02-12',0)

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

         Unique

The unique constraint is used to restrict duplicate values to be entered for a column of a database table.
create table customer(custid int unique,cname varchar(50),balance decimal(9,2),AOD date,status bit)
insert into customer values(101,'Ajay',10000,'2014-01-01',1)
(1 row(s) affected)

insert into customer values(101,'Vijay',9000,'2014-01-01',1)

Violation of UNIQUE KEY constraint 'UQ__customer__973AFEFF0519C6AF'. Cannot insert duplicate key in object 'dbo.customer'. The statement has been terminated.

Here, Sql server provides a unique hexadecimal name to the unique constraint which is not user friendly. So we need to provide a user defined name to the unique constraint as follows: -

create table customer(custid int constraint custid_uq unique,cname varchar(50),balance decimal(9,2),AOD date,status bit)
insert into customer values(101,'Karthik',50000,'2014-09-12',1)
(1 row(s) affected)

insert into customer values(101,'Ajay',40000,'2014-09-12',1)
Violation of UNIQUE KEY constraint 'custid_uq'. Cannot insert duplicate key in object 'dbo.customer'.
The statement has been terminated.

Enforcing Check and default constraints on columns of a database table

       Check
The check constraint is used to impose an user-defined constraint on a column of a database table.
create table customer(custid int,cname varchar(50),balance decimal(9,2) check(balance>=500),status bit)
insert into customer values(101,'Ajay',400,0)
The INSERT statement conflicted with the CHECK constraint "CK__customer__balanc__0AD2A005". The conflict occurred in database "Sql8", table "dbo.customer", column 'balance'.
The statement has been terminated.
insert into customer values(102,'vijay',500,1)
(1 row(s) affected)

      Default
The default constraint is used to provide user-defined values to a column of a database table in place of null.
create table customer(custid int,cname varchar(50),balance decimal(9,2),AOD date,status bit default 1)
insert into customer(custid,cname,balance,AOD) values(101,'vijay',20000,'2014-07-02')
insert into customer(custid,cname,balance,AOD) values(102,'ajay',20000,'2014-07-02')
insert into customer(custid,cname,balance,AOD) values(103,'vinay',20000,'2014-07-02')

Enforcing Primary key constraint on columns of a database table

The primary key constraint is used to restrict null and duplicate values to be entered for a column of a database table.
create table customer(custid int primary key,cname varchar(50),balance decimal(9,2),AOD date,status bit)
Cannot insert the value NULL into column 'custid', table 'master.dbo.customer'; column does not allow nulls. INSERT fails.
The statement has been terminated.
insert into customer values(101,'ajay',23000,'2014-09-11',1)
select *from customer
insert into customer values(101,'vijay',20000,'2014-08-01',0)
Violation of PRIMARY KEY constraint 'PK__customer__973AFEFE4FD1D5C8'. Cannot insert duplicate key in object 'dbo.customer'.
The statement has been terminated.

   

The column on which we impose a primary key constraint is called as identity or key column as this column helps us to uniquely identify a row in a database table.

A table must have only one primary key imposed on it. This primary key can be a composite primary key constraint on it, i.e. a single primary key can be imposed on a single column or multiple columns.

Enforcing not null, primary key,check and default constraints on columns of a database table(column-level imposition)

create table customer(custid int constraint custid_pk primary key,cname varchar(50)constraint cname_notnull not null,balance decimal(9,2)constraint balance_ck check(balance>=500),status bit constraint status_st default 0)
insert into customer values(101,'Ajay',10000,default)
(1 row(s) affected)
insert into customer values(101,'Vijay',10000,default)
Violation of PRIMARY KEY constraint 'custid_pk'. Cannot insert duplicate key in object 'dbo.customer'.The statement has been terminated.

insert into customer values(null,'Vijay',10000,default)
Cannot insert the value NULL into column 'custid', table 'master.dbo.customer'; column does not allow nulls. INSERT fails.
The statement has been terminated.

insert into customer values(102,null,10000,default)
Cannot insert the value NULL into column 'cname', table 'master.dbo.customer'; column does not allow nulls. INSERT fails.
The statement has been terminated.

insert into customer values(103,'Satish',200,default)
The INSERT statement conflicted with the CHECK constraint "balance_ck". The conflict occurred in database "master", table "dbo.customer", column 'balance'.
The statement has been terminated.

insert into customer(custid,cname,balance) values(102,'suresh',4000)

select *from customer



Enforcing primary key and check constraints on columns of a database table (table-level imposition)
create table customer(custid int,cname varchar(50),balance decimal(9,2),status bit,constraint custid_pk primary key(custid),constraint balance_ck check(balance>=500))
insert into customer values(101,'Ajay',400,1)
The INSERT statement conflicted with the CHECK constraint "balance_ck". The conflict occurred in database "master", table "dbo.customer", column 'balance'.
The statement has been terminated.

insert into customer values(null,'Ajay',400,1)
Cannot insert the value NULL into column 'custid', table 'master.dbo.customer'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Note: not null and default constraints cannot be imposed at table level.
The advantage of using table level imposition is that, we can impose a constraint on more than one column of a database table. Which can be explained in the examples given below: -
create table customer(custid int,cname varchar(50),constraint custidname_uq unique(custid,cname))

select *from customer




create table customer(custid int,cname varchar(50),balance decimal(9,2),AOD date,status bit,constraint cust_id_name primary key(custid,cname))
insert into customer values(101,'vijay',12000,'2014-09-03',1)
insert into customer values(101,'ajay',13400,'2014-08-03',1)

     


A constraint which can be applied on more than one column of a database table is called as composite constraint. In the above given examples the constraints unique and primary key are applied on two columns i.e. cname and custid of customer table.
When a constraint is applied on more than one column of a database table, then that constraint is satisfied if the value of any one of the column satisfies the constraint.

                                     Referential Integrity Constraint

This constraint is used to establish relationship between tables of a database so that data related to both the tables can be easily retrieved.
In the below given example, there are two tables: -
(i)                Department: - This is a master table as it is the first table in the relationship.
(ii)             Employee: - this is a detail table.


When we enter the details of an employee in employee table. The employee should be working in a department having its details in department table. Now to establish this relation between these two tables, we need a column which exists in both the tables. 

That column may not have same name in both the tables but its data type should be same in both the tables.For ex: -
create table department(deptno int,deptname varchar(50) not null,dloc varchar(20) not null,constraint depno primary key(deptno))



In the above given table, the ‘depno’ is an identity column which uniquely identifies every department whose details are entered in department table.

create table employee(empid int,ename varchar(50) not null,desg varchar(50) not null,dno int,constraint empid_pk primary key(empid),constraint dno_fk foreign key(dno) references department(deptno))


 
In the above given employee table, ‘dno’ is a column which is common for both the master and detail tables(i.e. department and master table).In the master table, it is named as ‘deptno’ and in the detail table it is named as ‘dno’. This column is called as reference key column in the  master table (department) and it is called as foreign key column in the detail table. (employee).

The foreign key column ‘dno’ of the detail table (employee) is only responsible for establishing relationship between the master and detail tables as it refers to the reference key column ‘deptno’ of the master table (department) for inserting a new record into the detail table and for retrieving data related to both the tables.

Now when a relation is established between the master and detail tables, following rules are enforced: -
     1) We can insert a new record in the the detail table, only when both the reference key column of the master table and foreign key column value of that record in the the detail table contain same values  as shown below: -

insert into employee values(204,'Uma','CEO',104)

The INSERT statement conflicted with the FOREIGN KEY constraint "dno_fk". The conflict occurred in database "emp", table "dbo.department", column 'deptno'.
The statement has been terminated.

    2)We cannot delete a record from the master table when the reference key column value of that record has corresponding child records in the detail table without deciding about what to do with those child records as shown below: -

delete from department where deptno=101

The DELETE statement conflicted with the REFERENCE constraint "dno_fk". The conflict occurred in database "emp", table "dbo.employee", column 'dno'.
The statement has been terminated.
   
   3) We cannot update a value of the reference key column of the master table without deciding about what to do with its corresponding child records as shown below: -

update department set deptno=104 where deptno=102

The UPDATE statement conflicted with the REFERENCE constraint "dno_fk". The conflict occurred in database "emp", table "dbo.employee", column 'dno'.
The statement has been terminated.
4   
    4) The master table can be deleted from the database only after deleting the detail table.

drop table department

Could not drop object 'department' because it is referenced by a FOREIGN KEY constraint.

Cascading Rules
By using cascading referential integrity constraints we can define the actions to be taken when a user tries to delete or update a record in the master table having a reference key column value which has corresponding child records in the detail table.

1.     On delete no action
Specifies that, if an attempt is made to delete a record from the master table having a reference key value which has corresponding child records in the detail table then, an error is raised and delete statement will not execute.

2.     On update no action
Specifies that, if an attempt is made to update a reference key column value then, an error is raised and update statement will not execute.
Thus,when no action is specified we can delete or update rows in the master table if they are referenced by detail table rows, but we can perform those operations, when we use cascade, set null and set default clauses as follows: -

3.     on delete set null
If we want to set the foreign key column values of all the child records to null  while deleting a record in a master table whose reference key column value has corresponding child records in the detail table, then  we should use on delete set null command while creating the details table as follows: -

create table department(deptid int,dname varchar(50) not null,dloc varchar(50) not null,constraint depid primary key(deptid))

  



create table employee(empid int,ename varchar(50) not null,edesg varchar(50) not null,deno int constraint d_fk  foreign key(deno) references department(deptid) on delete set null,constraint ed primary key(empid))


delete from department where deptid=101

select *from department



select *from employee


4.     On update set null

create table department(deptid int, dname varchar(50) not null, dloc varchar(50) bot null,constraint depid primary key(deptid))

        
 

create table employee(empid int,ename varchar(50) not null,edesg varchar(50) not null,depno int,constraint empid_pk primary key(empid),constraint dno foreign key(depno)references department(deptid)on update set null)


update department set deptid=401 where deptid=101
select *from department

select *from employee



5.     on delete cascade

If we want to delete a record in a master table whose reference key column value has corresponding child records in the detail table, then we should use on delete cascade command which deletes the record in master table along with all the corresponding child records in the detail table as follows: -

create table department(deptid int,dname varchar(50) not null,dloc varchar(50) not null,constraint depid primary key(deptid))

  


create table employee(empid int,ename varchar(50) not null,edesg varchar(50) not null,depno int,constraint empid_pk primary key(empid),constraint dno foreign key(depno) references department(deptid) on delete cascade)



delete from department where deptid=101

select *from department

           

select  *from employee




6.     on update cascade

create table department(deptid int,dname varchar(50) not null,dloc varchar(50) not null,constraint depid primary key(deptid))




create table employee(empid int,ename varchar(50) not null,edesg varchar(50) not null,dno int,constraint eid primary key(empid),constraint deno foreign key(dno) references department(deptid) on update cascade)




update department set deptid=401 where deptid=101

select*from department orderby dname


select *from employee




7.     on delete set default

create table department(deptid int constraint dep primary key,dname varchar(50) not null)


create table employee(empid int constraint emp primary key,ename varchar(50) not null,dno int default 60,constraint deno foreign key(dno) references department(deptid) on delete set default)


delete from department where deptid=101

The DELETE statement conflicted with the FOREIGN KEY constraint "deno". The conflict occurred in database "emp", table "dbo.department", column 'deptid'.
The statement has been terminated.


8.     on update set default

create table department(deptid int constraint dep primary key,dname varchar(50) not null)


create table employee(empid int constraint emp primary key,ename varchar(50) not null,dno int default 60,constraint deno foreign key(dno) references department(deptid) on update set default)



update department set deptid=301 where deptid=101

The UPDATE statement conflicted with the FOREIGN KEY constraint "deno". The conflict occurred in database "emp", table "dbo.department", column 'deptid'.

The statement has been terminated.

Tags:
About the Author

I am Azeheruddin Khan having more than 6 year experience in c#, Asp.net and ms sql.My work comprise of medium and enterprise level projects using asp.net and other Microsoft .net technologies. Please feel free to contact me for any queries via posting comments on my blog,i will try to reply as early as possible. Follow me @fresher2programmer
View all posts by admin →

Get Updates

Subscribe to our e-mail newsletter to receive updates.

Share This Post

0 comments:

adsense

© 2014 Fresher2Programmer. WP Theme-junkie converted by Bloggertheme9
Powered by Blogger.
back to top