Constraints

Constraints

Introduction:

ð Constraints are used to prevent invalid data entry into tables and thereby maintain the integrity of the data.

ð They are otherwise called as business rule. 

There are two levels of constraints, 

1.     Column level constraints

2.     Table level constraints 

Column level constraints:

ð It is in the part of the column definition and is imposed only on the column  in which the constraint is defined. 

Table level constraints

ð This is a part of the table definition. Constraints defined in the table level can restrict the data in any column in the table.

3 Types of Table level constraints,

1.     Domain constraint

2.     Entity constraint

3.     Referential integrity constraint

Domain constraint:

ð Which is used to check the values entered into a particular column is valid or not. 

§  NOT NULL 

§  Check

NOT NULL:

ð Which is used retrieve the column doesn’t have null values. 

Example:  

Create table library ( Book_no  number,  

   Book_name varchar2(50),

                                   prize number);

Table:  library

Book_no	Book_name	Prize

 insert into library (Book_no) values ('SQL')     

ð  The above query shows error

insert into library (Book_no,Book_name) values(100,’SQL’);        

ð  It does not shows error

Book_no	Book_name	Prize
100	SQL	2000

Check Constraint

ð Check Constraints are used to checking the column values 

ð  here we define some logical expression and relational expression

Some checks constraints, 

IN, NOT IN , BETWEEN, LIKE 

IN

Select  Book_no,Book_name from Library where Book_no  in (100,101);

NOT IN

Select  Book_no,Book_name from Library where Book_no not  in  (100,101);

BETWEEN

Select  * from  Library where Book_no  between 101 and 103;

LIKE

Select *  from library  Where Book_name like ‘S%’

Some Examples for using LIKE in different ways,

S.NO	Statement	Description
1	Where Book_name like ‘S%’	Find any values that starts with S.
2	Where Book_name like ‘%S%’	Find any values that have S in any position
3	Where Book_name like ‘_S%’	Find any values that have S in second position
4	Where Book_name like ‘S_%_%’	Find any values that starts  with S and are at least 3 characters in length
5	Where Book_name like ‘%S’	Find any values that ends with S
6	Where Book_name like ‘_S%L’	Find any values that have a S in Second position and end with aL
7	Where Book_name like ‘S___M’	Find any values in a five digit characters starts with S and ends with M

Entity integrity constraints

ð  This are used to avoid duplicate values. 

          Two types,

1. Primary key

2. Unique

PRIMARY KEY:

ð Here we define a column or group of column should be primary key, it doesn’t allow duplicate values and null value

Syntax:

                Create table table_name

                (clm_name data_type PRIMARY KEY);

Example: 

Create table Ex(no number(4)primary key,name varchar2(20));

Composite primary key

 Create table ex1(no number(4),name varchar2(20), dept number(4),primary     key(no,dept)); 

ð  It checks two columns. if the two columns having same data,It shows    error. 

UNIQUE:

ð  Here we define a column or group of column should be primary key

ð  It doesn’t allow duplicate value.

ð  It allows any number of null values 

Create table Ex2(no number(4)unique, name varchar2(20)); 

Composite unique key

ð A unique key constraints more than one column called a composite unique key. The maximum number of columns in a composite unique key is 16.

Example:  

         Create table Ex3(no number(4),name varchar2(20), dep number(4),         unique(no,dept)):

Share: