Email Us : sunilkumark11@gmail.com
Free Training > SQL PL/ SQL Forms Reports Oracle Applications
< Previous Next >

 
SQL Constraints

Constraints are rules appled on tables.
Constraints increase in integrity or quality of the database.

Types of Constraints:
----------------------
1) NOT NULL
2) UNIQUE
3) PRIMARY KEY
4) FOREIGN KEY (or) Referential Integrity Constraint
5) CHECK

Declaration Style:
-------------------
Constraint can be created in two levels
1) Column Level
2) Table Level

Column Level:
---------------
They are declared as part of the definition of the individual column.

Table Level:
----------------
They are declared as part of the table definition.

 

1) NOT NULL Constraint:
------------------------
NOT NULL constraint does not accept NULL values.
To satisfy this constraint, every row in the table must contain a value.
NOT NULL constraint can be created only at column level.

Syntax:
-----------

Create table < table_name> ( col_name1 datatype(size) NOT NULL,
col_name2 datatype(size) ,
col_namen datatype(size) );

Ex:
---

Create table student1 ( sno number(3) NOT NULL,
sname varchar2(10),
marks number(3));

insert into student1 values ( 101,'arun', 40); -- valid
insert into student1 values ( 102,'kiran', 75); -- valid
insert into student1 values ( null,'arun', 40); -- error

UNIQUE COnstraint:
-----------------------

UNIQUE constraint does not accept duplicate value.
UNIQUE constraint can be created at column level or at table level.
UNIQUE constraint will accept NULL value.

Syntax of UNIQUE constraint at column level:
----------------------------------------------

Create table < table_name> ( col_name1 datatype(size) UNIQUE,
col_name2 datatype(size),
col_namen datatype(size));

Ex:
----

Create table student2 ( sno number(3) UNIQUE,
sname varchar2(10),
marks number(3));

insert into student2 values ( 101, 'arun',40); -- valid
insert into student2 values ( 102, 'kiran',75); -- valid
insert into student2 values ( 101, 'vijay',55); -- error
insert into student2 values ( null,'ashok',85); -- valid
insert into student2 values ( null,'vinay',81); -- valid

Note:
------
UNIQUE constraint will accept any no of NULL values

Syntax of UNIQUE constraint at table level:
-------------------------------------------------

Create table < table_name> ( col_name1 datatype(size),
col_name2 datatype(size),
col_namen datatype(size),
UNIQUE ( col_name1));

Ex:
-----
Create table student3 ( sno number(3) ,
sname varchar2(10),
marks number(3),
UNIQUE ( sno ));

insert into student3 values ( 101, 'arun',40); -- valid
insert into student3 values ( 102, 'kiran',75); -- valid
insert into student3 values ( 101, 'vijay',55); -- error
insert into student3 values ( null,'ashok',85); -- valid
insert into student3 values ( null,'vinay',81); -- valid

There is no difference practically , when a constraint is created at column level
or table level. You can follow any syntax.

PRIMARY KEY:
------------------

A PRIMARY KEY constraint is combination of NOT NULL and UNIQUE constraint.
A PRIMARY KEY column will not accept NULL values and DUPLICATE values.
A PRIMARY KEY column is used to uniquely identify every row in the table.
A Table can have only one PRIMARY KEY
A PRIMARY KEY Constraint can be created at column level or at table level.

Syntax of PRIMARY KEY at Column Level:
-----------------------------------------

Create table < table_name> ( col_name1 datatype(size) PRIMARY KEY,
col_name2 datatype(size),
col_namen datatype(size));

Ex:
--------

Create table student4 ( sno number(3) PRIMARY KEY,
sname varchar2(10),
marks number(3));

insert into student4 values ( 101, 'arun',40); -- valid
insert into student4 values ( 102, 'kiran',75); -- valid
insert into student4 values ( 101, 'vijay',55); -- error
insert into student4 values ( null,'ashok',85); -- error

Syntax of PRIMARY KEY at table level:
---------------------------------------

Create table < table_name> ( col_name1 datatype(size),
col_name2 datatype(size),
col_namen datatype(size),
PRIMARY KEY ( col_name1));

Ex:
-----
Create table student5 ( sno number(3) ,
sname varchar2(10),
marks number(3),
PRIMARY KEY ( sno ));

insert into student5 values ( 101, 'arun',40); -- valid
insert into student5 values ( 102, 'kiran',75); -- valid
insert into student5 values ( 101, 'vijay',55); -- error
insert into student5 values ( null,'ashok',85); -- valid
insert into student5 values ( null,'vinay',81); -- valid

There is no difference practically , when a constraint is created at column level
or table level. You can follow any syntax.

FOREIGN KEY Constraint:
----------------------------

A Foreign key constraint establishes relationship between tables.
This relationship is called as Parent-child relationship.
It is also called as Master-Detail relationship.
A Foreign Key column in child table will only accept the values present in the Primary Key or Unique column of the parent table.
A foreign key constraint can be created at column level or at table level.

TO understand this FOREIGN KEY Constraint , have a look at this scenario:
-------------------------------------------------------------------------------

I have started a school.
Assume only 3 students joined in my school.
I create a table with name school and enter the student details in it. Every student will have student no. So, sno is primary Key

SCHOOL

Sno

Sname

Gender

Age

101

arun

M

5

102

kiran

M

5

103

sreeja

F

4

 

I have appointed librarian for my school. If any student borrows book, librarian should enter the sno and book name .
I create a table with name library . Every sunday i visit my school to check whether it is running properly or not, surprisingly , i have the following data in my library table.

Library

sno

book_name

102

oracle

103

java

103

c++

108

unix

If you observe, there is no student 108 in our school.
So the last row is invalid row. Probably this could be data entry mistake.
I do not want these kind of inavlid data into my library table.
That means, i should create library table in such a way thay it should accept only the sno values which are in school table.
So, we should create a relationship between these table. This relationship is called as parent-child relationship.

In our scenario,
School is the parent table.
Library is the child table.
sno column in the library table is called as FOREIGN KEY column.
Note: A Foreign key column in the child table will only accept the values present in the PRIMARY KEY column of the parent table.


Lets practically workout this scenario
---------------------------------------------------
Step 1: Creating the parent table
Step 2: Inserting rows into the parent table
Step 3: Creating child table which contains FOREIGN KEY
Step 4: Inserting rows into the child table and understand the behaviour of the FOREIGN KEY column

Step 1: Creating parent table
-----------------------------------------
create table school ( sno number(3) PRIMARY KEY,
sname varchar2(10),
gender varchar2(10),
age number(2));

Step 2: Inserting rows into the parent table
-----------------------------------------------------------
insert into school values ( 101, 'arun', 'M' , 5);
insert into school values ( 102, 'kiran', 'M' , 5);
insert into school values ( 103, 'sreeja', 'M' , 4);

Step 3: creating child table
Syntax of foreign key constraint at column level
----------------------------------------------------------------
create table <table_name> ( col_name1 datatype(size),
col_name2 datatype(size),
col_name3 datatype(size) REFERENCES <parent table > ( primary key col_name));

Ex:
------
create table library ( sno number(3) REFERENCES school ( sno),
book_name varchar2(10));

Step 4: Insertingrow into the child table
----------------------------------------------------
insert into library values ( 102, 'oracle'); -- valid
insert into library values ( 103, 'java'); -- valid
insert into library values ( 103, 'c++'); -- valid
insert into library values ( 108, 'unix'); -- error
Note:
As we have established relationship, library table is not accepting sno 108.
This relationship helps in improving the accuracy of the database.
++++++++++++++++++++++++

Syntaxof Foreign key constraint at table level
create table <table_name> ( col_name1 datatype(size),
col_name2 datatype(size),
FOREIGN KEY ( col_name1) REFERENCES < parent table> ( primary key col name ) );

Ex:
------
create table library ( sno number(3),
book_name varchar2(10) REFERENCES school ( sno));

Note:
A FOREIGN KEY column will accept duplicate values
A FOREIGN KEY column will accept null values.

CHECK Constraint:
-------------------------------
Check constraint is used to define values a column can store.

Ex:
----------

Create table student6 ( sno number(3),
Sname varchar2(10),
Marks number(3) CHECK ( marks between 0 and 100) );

Insert into student6 values ( 101, arun, 60);
Insert into student6 values ( 102, vijay, 55);
Insert into student6 values ( 103, amit, 120); -- error -- check constraint violated
Insert into student6 values ( 104, amit, -10); -- error -- check constraint violated

 

< Previous Next >