Constraints In Oracle

 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';
---------------------------------------------------------------