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.