Oracle Management Constraints (IV) ORACLE CORRECTION constraint data

When you activate a constraint using the Enable VALIDATE option, the error message is prompted if the data already exists that does not meet the constraint rule, and the activation constraint fails. The existing data must be corrected at this time, ensuring that the data is fully mouthed with the constraint rules. The specific methods are as follows:

(1) Establish exceptions table

05:02:44 sql> @ $ORACLE _home/rdbms/admin/utlexcpt;

Table created.

05:05:33 sql> ALTER TABLE T1

05:06:26 2 Add Constraint pk_id primary key (DEPTNO);

Table altered.

05:07:04 sql> Select Constraint_name,constraint_type,status,table_name from user_constraints

05:07:14 2 where table_name= ' T1 ';

Constraint_name C STATUS table_name

------------------------------ - -------- ------------------------------

pk_id P ENABLED T1

U_name U-ENABLED T1

05:07:17 sql> ALTER TABLE T1

05:07:29 2 disable Novalidate primary key;

Table altered.

05:07:53 sql> Select Constraint_name,constraint_type,status,table_name from user_constraints

05:07:57 2 where table_name= ' T1 ';

Constraint_name C STATUS table_name

------------------------------ - -------- ------------------------------

pk_id P DISABLED T1

U_name U-ENABLED T1

05:08:24 sql> INSERT INTO T1 values (' Oracle ', ' BeiJing ');

1 row created.

05:09:00 sql> INSERT INTO T1 values (' Cuug ', ' BeiJing ');

1 row created.

05:09:15 sql> SELECT * from T1;

DEPTNO NAME LOC

---------- -------------------- --------------------

Ten Cisco BeiJing

C%OMMD Shanghai

COMMD Shanghai

Microsoft Shanghai

Oracle BeiJing

Cuug BeiJing

6 rows selected.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

(2) Activation constraint

05:09:52 sql> ALTER TABLE T1

05:09:54 2 Enable validate primary key exceptions into exceptions;

ALTER TABLE T1

*

ERROR at line 1:

Ora-02437:cannot Validate, SCOTT. PK_ID)-primary key violated

(3) Determine rows that do not meet the constraint rule

05:12:19 sql> Select Deptno, rowid from T1

05:12:21 2 where rowID in (select row_id to exceptions) for update;

DEPTNO ROWID

---------- ------------------

Ten AAANQPAAEAAAAHEAAA

Aaanqpaaeaaaahfaaa

Ten Aaanqpaaeaaaahhaab

Aaanqpaaeaaaahhaac

(4) Correction of data

05:13:49 sql> Select deptno from T1;

DEPTNO

----------

10

20

30

40

10

20

6 rows selected.

05:13:51 sql> Update t1 set deptno=50 where rowid= ' Aaanqpaaeaaaahhaab ';

1 row updated.

05:14:27 sql> Update t1 set deptno=60 where rowid= ' AAANQPAAEAAAAHHAAC ';

1 row updated.

05:14:43 sql> Select deptno from T1;

DEPTNO

----------

10

20

30

40

50

60

6 rows selected.

(5) Activation constraint

05:15:44 sql> ALTER TABLE T1

05:15:45 2 enable validate primary key;

Table altered.