1)constraint
1@@@@constraint state
@@@
@@@<1>concept
@@@
Constraint States:
  ENABLE: ensures that all incoming data conforms to the constraint
  DISABLE: allows incoming data, regardless of whether it conforms to the constraint
  VALIDATE: ensures that existing data conforms to the constraint
  NOVALIDATE: means that some existing data may not conform to the constraint
  DEFERRED: only check while you commit transaction, if this deferred constraint is violated,
            undo this transaction.
  NO DEFERRED: check immediate while every sql statement, if this non-deferred constranit is violated
               , only undo current statement.    
  DEFERABLE: allow deferred, but no deferred right now. It is assgined only when create this constraint.
  INITIAL DEFERED/IMMEDIATE: deferable in advance, initial deferred => DEFERRED(above)
                             initial immediate =>  NO DEFERRED(above)
  alter session set constraints=immediate/deferred;

Note:
  DEFERABLE is absolutely a useless technology.
 

@@@
@@@<2>validate is useless
@@@
SH@ocp> /
  1  create table p_table
  2  (id number primary key,
  3   grade number not null,
  4   person_id number not null,
  5   constraint person_id_uk unique(person_id),
  6   constraint grade_ck check (grade > 0 and grade < 100)
  7* )
SH@ocp> /
Table created.
SH@ocp> insert into p_table values(1,64,3641);
SH@ocp> insert into p_table values(2,73,3642);
SH@ocp> insert into p_table values(3,77,3643);
SH@ocp> commit;

@@@
@@@disable the unique constraint, then disobey deliberatly.
SH@ocp> alter table p_table disable constraint person_id_uk ;
SH@ocp> insert into p_table values(4,73,3643);
SH@ocp> commit;

@@@
SH@ocp> alter table p_table modify constraint person_id_uk enable novalidate;
alter table p_table modify constraint person_id_uk enable novalidate
*
ERROR at line 1:
ORA-02299: cannot validate (SH.PERSON_ID_UK) - duplicate keys found

SH@ocp> alter table p_table enable constraint person_id_uk ;
alter table p_table enable constraint person_id_uk
*
ERROR at line 1:
ORA-02299: cannot validate (SH.PERSON_ID_UK) - duplicate keys found

@@@
@@@drop constraint, then recreate constraints, it didn't work.
SH@ocp> alter table p_table drop constraint person_id_uk ;
Table altered.

SH@ocp> alter table p_table add constraint person_id_uk unique(person_id) enable novalidate;
alter table p_table add constraint person_id_uk unique(person_id) enable novalidate
                                   *
ERROR at line 1:
ORA-02299: cannot validate (SH.PERSON_ID_UK) - duplicate keys found


Summary:
   enable or disable directly were useful. validate or novalidate didn't work well.


@@@
@@@<3>handle exception
@@@
SH@ocp> drop table p_table;
Table dropped.

SH@ocp> ed
  1  create table p_table
  2     (id number primary key,
  3      grade number not null,
  4      person_id number not null,
  5      constraint person_id_uk unique(person_id),
  6      constraint grade_ck check (grade > 0 and grade < 100)
  7*   )
SH@ocp> /
Table created.

SH@ocp> insert into p_table values(1,64,3641);
SH@ocp> insert into p_table values(2,73,3642);
SH@ocp> insert into p_table values(3,77,3643);
SH@ocp> commit;
SH@ocp> alter table p_table disable constraints person_id_uk ;
SH@ocp> insert into p_table values(4,37,3643);
SH@ocp> commit;

SH@ocp> select * from exceptions where table_name='P_TABLE';
ROW_ID           OWNER
------------------ ------------------------------------------------------------------------------------------
TABLE_NAME
------------------------------------------------------------------------------------------
CONSTRAINT
------------------------------------------------------------------------------------------
AAANqeAAEAAADl8AAD SH
P_TABLE
PERSON_ID_UK

AAANqeAAEAAADl8AAC SH
P_TABLE
PERSON_ID_UK


SH@ocp> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlexcpt.sql
SH@ocp> select * from p_table where rowid in (select row_id from exceptions where table_name='P_TABLE');
    ID    GRADE  PERSON_ID
---------- ---------- ----------
     3       77        3643
     4       37        3643

SH@ocp> delete from p_table where id=4;
1 row deleted.
SH@ocp> commit;
SH@ocp> alter table p_table enable constraints person_id_uk;
Table altered.