Oracle管理約束（四）Oracle修正約束資料

使用 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.

查看本欄目更多精彩內容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

（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.