Friday, October 28, 2011

ORACLE SQL TUTORIAL: DEFERRABLE CONSTRAINTS


DEFERRABLE CONSTRAINTS

Each constraint has two additional attributes to support deferred checking of constraints.

Ø  Deferred initially immediate
Ø  Deferred initially deferred

Deferred initially immediate checks for constraint violation at the time of insert.
Deferred initially deferred checks for constraint violation at the time of commit.

Ex:
     SQL> create table student(no number(2), name varchar(10), marks number(3), constraint
             un unique(no) deferred initially immediate);

     SQL> create table student(no number(2), name varchar(10), marks number(3), constraint
             un unique(no) deferred initially deferred);

     SQL> alter table student add constraint un unique(no) deferrable initially deferred;
     
      SQL> set constraints all immediate;
     This will enable all the constraints violations at the time of inserting.
      SQL> set constraints all deferred;
     This will enable all the constraints violations at the time of commit.



0 Responses to “ORACLE SQL TUTORIAL: DEFERRABLE CONSTRAINTS”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.