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.
转载于:https://blog.51cto.com/majesty/977666