管理约束（四）--修正约束数据

 

使用 ENABLE VALIDATE 选项激活约束时，如果已存在数据不满足约束规则，则会提示错误信息，并且激活约束会失败。此时必须对已存在 数据进行修正，已确保这些数据全部满嘴约束规则。具体方法如下：

（1）建立EXCEPTIONS 表

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 (10,'Oracle','BeiJing');

1 row created.

05:09:00 SQL> insert into t1 values (20,'Cuug','BeiJing');

1 row created.

05:09:15 SQL> select * from t1;

DEPTNO NAME                 LOC

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

10 Cisco                BeiJing

20 C%ommd               ShangHai

30 Commd                ShangHai

40 Microsoft            ShangHai

10 Oracle               BeiJing

20 Cuug                 BeiJing

6 rows selected.

（2）激活约束

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）确定不满足约束规则的行

05:12:19 SQL> select deptno ,rowid from t1

05:12:21   2    where rowid in (select row_id from exceptions) for update;

DEPTNO ROWID

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

10 AAANQPAAEAAAAHEAAA

20 AAANQPAAEAAAAHFAAA

10 AAANQPAAEAAAAHHAAB

20 AAANQPAAEAAAAHHAAC

（4）修正数据

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）激活约束

05:15:44 SQL> alter table t1

05:15:45   2   enable validate primary key ;

Table altered.