Data Integrity Rules { Constraints }:
A set of pre-defined rules applied on table columns while creating Tables or after creation.
They are automatically activated when ever "DML" operations are performed on tables.
They are used to impose restrictions on Table Columns.
They are also activated when Tables are manipulated by other users or by other Application s/w Tools.
They provide High security on Tables.
3 Types
1. Domain Integrity Rules
Used to restrict duplicate values into table columns.
ex: Unique , Primary Key
2. Entity Integrity Rules
Used to provide conditional restrictions on Table columns.
ex: Check , Not null
3. Referential Integrity Rule
Used to establish relationship between 2 tables.
ex: References ( Foreign Key )
Oracle Constraints :
* Not null : Used to restrict null values , Any no.of duplicates are allowed. ( Column Level Constraint )
* Unique : Used to restrict duplicate values but any no.of null values are allowed.
( 2 null values are not equal )
* Check : Used to provide conditional restrictions on table columns.
* Default : Used define initail value for a column
( Column Level property ).
If column is not assigned with a value then default value will be accepted.
* Primary Key : Not Null + Unique + Index
Used to define the Key column of a table.
It can be used only once in Table definition.
It will not allow Null values and Duplicate values into Key column.
It is supported with an Index automatically.
Index :
It is a pointer locates the physical address of data.
It will improve performance of oracle while Retrieving or Manipulating data using Key column.
It is automatically activated whenever key column is used in "Where" clause.
* References ( Foreign Key ) :
Used to define relationship between 2 Tables.
It allows Null and duplicate values .
It can be related to either Primary key or unique constraint column of other Table.
PK / UNQ <-----> FK
T1
T2
---
---
c1 (pk/unq)
c1 (fk) T1.c1 = T2.c1
---------------------------------------------------------------
dept
emp
------
------
deptno (pk)
deptno (fk)
dept.deptno = emp.deptno
* one - many or many - one Relation established
between PK and FK.
---------------------------------------------------------------
Note:
Constraints are defined in 2 Methods:
1. Column Constraint Syntax
2. Table Constraint Syntax
1. Column Constraint Syntax
constraints are defined at the end of column definition.
All constraints are supported.
Used to define constraints only while creating Tables
Using Column constraint Syntax:
create table dept
(deptno number(2) primary key,
dname varchar2(20) not null unique,
loc varchar2(20) default 'Hyderabad');
insert into dept values (10,'SALES','MUMBAI');
insert into dept values (null,'HR','PUNE');
insert into dept values (10,'ADMIN','NOIDA');
insert into dept values (20, null ,'Secbad');
insert into dept values (30,'Sales','GOA');
---------------------------------------------------------------
Activating default :
insert into dept values(40,'TRAINING',default);
* Default : keyword (8.0)
used to replace with default value defined
insert into dept(deptno,dname) values(50,'TESTING');
insert into dept values(60,'RESEARCH',null);
insert into dept values(70,'HR'); -- error
select * from dept;
40 TRAINING Hyderabad
50 TESTING Hyderabad
60 RESEARCH
---------------------------------------------------------------
select * from dept where deptno = 10;
update dept set loc = 'VIZAG' where deptno = 30;
-- Index is activated automatically while Retrieving or Manipulating data thru key column in WHERE clause.
---------------------------------------------------------------
Create table emp
(empno number(4) primary key,
ename varchar2(20) not null,
sex char(1) check (sex in ('M','F')),
sal number(12,2) check (sal >= 5000),
hiredate date default sysdate,
mail_id varchar2(100) unique,
deptno number(2)
references dept on delete cascade );
* emp.deptno = dept.deptno -- Join condition
dept - Master Table (parent) -- Independent Table
emp - Detail Table (child) -- Dependent Table
* dept - deptno (10,20,30,40)
insert into emp values(101,...........,10);
insert into emp values(102,...........,20);
insert into emp values(103,...........,30);
insert into emp values(104,...........,10);
insert into emp values(105,...........,10);
insert into emp values(108,...........,null);
insert into emp values(110,...........,90); -- error
err: Parent key not found (2291)
delete from dept where deptno = 10; -- error
err: Depending child rows exists - cannot remove parent ( 2292 )
create table incr(empno number(4) not null
references emp on delete cascade ,
amt number(10,2) not null);
incr.empno = emp.empno -- Join Condition
insert into incr values(101,10000);
insert into incr values(105,10000);
insert into incr values(103,5000);
insert into incr values(105,5000);
insert into incr values(null,10000); -- error (null value)
insert into incr values(999,10000); -- error
err: Parent key not found
insert
delete
1
Dept
3
|
2
Emp
2
|
3
Incr 1
If " On delete cascade " is not defined :
3. delete from dept where deptno = 10;
err - depending child rows exists
2. delete from emp where deptno = 10;
err - depending child rows exists
1. delete from incr where empno in
( select empno from emp where deptno = 10);
On delete cascade - Clause
Automatically removes the child rows whenever parent record is removed.
It has to be specified with every child table along with references constraint.
It cannot be assigned seperately.
It is activated by " Delete " stmt on parent table.
If On delete cascade is defined :
>delete from dept where deptno = 10;
-- Automatically removes dept 10 details from Emp, incr tables also.
drop table dept;
error : depending child rows exists
drop table dept cascade constraints;
Cascade constraints - clause
Allows to remove the parent table even if child exists.
It will destroy the relationship between 2 tables.
Child records still exists even if parent table is dropped.
It is used with "drop" stmt on parent table.
Oracle Constraint Error Nos:
Not null ---> -1400
Unique ---> -1
Check ---> -2290
References ---> -2291 -- Parent key not found
-2292 -- Depending child rows exists
---------------------------------------------------------------
create table stu_info( roll number(3) primary key,
sname varchar2(20) not null,
course varchar2(10)
check (course in ('oracle' , 'dwh' , 'unix' , 'd6i')),
fee number(5) check (fee >= 1000),
doj date default sysdate);
insert into stu_info values(101,'RAM','oracle',1000,sysdate);
update stu_info set fee = 500 where roll = 101;
-- error
---------------------------------------------------------------
create table course (cid char(6) primary key,
cname varchar2(20) not null,
faculty varchar2(20) not null,
room number(3),
start_date date);
ora10 oracle 11g Sridhar 511 28-feb-11
create table student(roll number(3) primary key,
sname varchar2(20) not null,
sex char(1) check (sex in ('M','F')),
cid char(6) references course,
email varchar2(30) unique,
fee number(5) check (fee >= 1000));
101 RAM M ora10 ram@gmail.com 2100
course.cid = student.cid --- join condition
create table fee_instalments
(roll number(3) not null references student ,
amt number(5) not null , date_paid date);
101 1000 28-feb-11
101 500 15-mar-11
101 600 30-mar-11
student.roll = fee_instalments.roll -- join condition
* Course --> student --> fee_instalments
---------------------------------------------------------------
Table constraint syntax:
Constraints are defined at the end of table definition.
Supports to define Composite Primary key(CPK) and Composite Foreign key (CFK).
Not null and Default are not allowed.
Used to define constraints on existing tables.
Max 32 columns can be defined in CPK or CFK.
Ex: Create table Reservation
( train_no number(4) ,
coach_id varchar2(5) ,
seat_no number(3) ,
doj date , pname varchar2(20),
age number(3), sex char(1),
to_stn varchar2(20), from_stn varchar2(20),
fare number(5),
constraint pk_rail
primary key (train_no, coach_id, seat_no, doj));
7025
7025 101 - oracle - 10:00
S1 S1 101 - oracle - 11:30
10 11 102 - oracle - 10:00
16-MAY-11 16-MAY-11 (roll - course - timing ) --- CPK
--------------------------------------------------------------
create table bankmaster
( accno number(4), acc_type char(1),
name varchar2(20) not null,
curr_bal number(12,2),
pan_no varchar2(15) ,
constraint pk_bank primary key(accno,acc_type),
constraint chk_atype check (acc_type in('S','C','R')),
constraint chk_bal check (curr_bal >= 5000),
constraint unq_pan unique(pan_no));
101 101 101 102 101 null 103
S R C null S S r
create table Transaction
( accno number(4), acc_type char(1),
tran_type char(1), tran_date date default sysdate,
amt number(12,2),
constraint fk_bank foreign key (accno,acc_type) references bankmaster on delete cascade,
constraint chk_ttype check (tran_type in ('W','D')),
constraint chk_amt check (amt >= 100));
Join condition :
bankmaster.accno = transaction.accno and
bankmaster.acc_type = transaction.acc_type
---------------------------------------------------------------
Adding Constraints to existing Tables:
Alter table dept add constraint pk_dept
primary key(deptno);
Alter table dept add constraint unq_dname unique(dname);
Alter table emp add constraint pk_emp
primary key(empno);
Alter table emp add constraint chk_sal
check(sal >= 3000);
Alter table emp add constraint fk_dept
foreign key(deptno)
references dept(deptno) on delete cascade;
Note:
* While adding constraints to existing tables with information existing data must satisfy constraint Rule.
Adding Not null & Default : ( Properties )
Alter table emp modify ename varchar2(20)
not null;
Alter table emp modify hiredate date default sysdate;
Removing Not null & Default : ( Properties )
Alter table emp modify ename varchar2(20) null;
Alter table emp modify hiredate date default null;
[ drop/disable/enable ]
Alter table <table name> drop constraint
<cons name>;
Alter table emp disable constraint chk_sal;
Alter table emp enable constraint chk_sal;
Alter table emp drop constraint chk_sal;
---------------------------------------------------------------
Scott: ( Sharing Constraints )
Grant References on dept to user1;
User1:
Create table employ (
empno number(4) constraint pk_emp primary key,
ename varchar2(20), sal number(12,2),
* mgr number(4) References employ,
deptno number(2) references scott.dept);
insert into employ values(101,......,null,10);
insert into employ values(102,......,101,10);
Join Conditions :
employ.empno = employ.mgr ( Self Join )
user1.employ.deptno = scott.dept.deptno
* Self Reference Key: (mgr)
Table Referencing to itself.
Same table acts as a Parent to itself .
Table must have similar column to apply this relation.
---------------------------------------------------------------
create table emp (empno number(4) primary key, .....);
Removing Primary Key:
Alter table emp drop constraint sys_c002345 ;
System Tables:
* User_constraints --- Holds the complicate details of constraints defined on table columns.
* User_cons_columns --- Holds the brief information about the constraints applied on table columns.
> desc user_constraints
> select * from user_constraints
where table_name = 'EMP';
> select constraint_name, constraint_type from user_constraints where table_name = 'EMP';
sys_c002345 P (PK)
sys_c002346 C (Chk / NN )
sys_c002347 U (Unq)
sys_c002348 R (Ref)
> desc user_cons_columns
> select * from user_cons_columns
where table_name = 'EMP';
---------------------------------------------------------------
* Display common column between 2 tables
select column_name from user_tab_columns
where table_name = 'DEPT'
intersect
select column_name from user_tab_columns
where table_name = 'EMP' ;
---------------------------------------------------------------
