Integrity 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 can be related to either Primary key or unique constraint column of other Table.
It allows Null and duplicate values .
T1 T2
--- ---
c1 (pk/unq) c1 (fk)
dept emp
------ ------
deptno (pk) deptno (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 while creating Tables only.
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');
select * from dept;
40 TRAINING Hyderabad
50 TESTING Hyderabad
---------------------------------------------------------------
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 >= 3000),
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
delete from dept where deptno = 10; -- error
err: Depending child rows exists - cannot remove parent
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(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.
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 student( roll number(3) primary key,
sname varchar2(20) not null,
course varchar2(10)
check (course in ('oracle' , 'java' , 'unix' , 'd6i')),
fee number(5) check (fee >= 1000),
doj date default sysdate);
insert into student values(101,'RAM','oracle',1000,sysdate);
update student set fee = 500 where roll = 101;
-- error
---------------------------------------------------------------
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 7026 101 - oracle - 10:00
S1 S1 101 - oracle - 11:30
10 10 102 - oracle - 10:00
14-feb-10 14-feb-10 (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
S R C null S S
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));
---------------------------------------------------------------
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;
Adding Not null & Default : ( Properties )
Alter table emp modify ename varchar2(20)
not null;
Alter table emp modify hiredate date default sysdate;
Note: * While adding constraints to existing tables with data existing data must satisfy constraint Rule.
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 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);
* Self Reference Key: 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,.....);
Alter table emp drop constraint sys_c002345 ;
System Tables:
* User_constraints --- Holds the compleate 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';
---------------------------------------------------------------
- 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 can be related to either Primary key or unique constraint column of other Table.
It allows Null and duplicate values .
T1 T2
--- ---
c1 (pk/unq) c1 (fk)
dept emp
------ ------
deptno (pk) deptno (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 while creating Tables only.
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');
select * from dept;
40 TRAINING Hyderabad
50 TESTING Hyderabad
---------------------------------------------------------------
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 >= 3000),
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
delete from dept where deptno = 10; -- error
err: Depending child rows exists - cannot remove parent
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(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.
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 student( roll number(3) primary key,
sname varchar2(20) not null,
course varchar2(10)
check (course in ('oracle' , 'java' , 'unix' , 'd6i')),
fee number(5) check (fee >= 1000),
doj date default sysdate);
insert into student values(101,'RAM','oracle',1000,sysdate);
update student set fee = 500 where roll = 101;
-- error
---------------------------------------------------------------
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 7026 101 - oracle - 10:00
S1 S1 101 - oracle - 11:30
10 10 102 - oracle - 10:00
14-feb-10 14-feb-10 (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
S R C null S S
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));
---------------------------------------------------------------
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;
Adding Not null & Default : ( Properties )
Alter table emp modify ename varchar2(20)
not null;
Alter table emp modify hiredate date default sysdate;
Note: * While adding constraints to existing tables with data existing data must satisfy constraint Rule.
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 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);
* Self Reference Key: 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,.....);
Alter table emp drop constraint sys_c002345 ;
System Tables:
* User_constraints --- Holds the compleate 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';
---------------------------------------------------------------
No comments:
Post a Comment