關于限制ENABLE NOVALIDATE的一個疑問

​

關于限制ENABLE NOVALIDATE的一個疑問

CREATE TABLE test
(id varchar2(12),
name varchar2(30) ,CONSTRAINT pk_test PRIMARY k

ALTER TABLE test disable CONSTRAINT pk_test      

然後向表中插入兩條完全一樣的資料并commit

我的印象是ENABLE NOVALIDATE并不檢查已經存在的數

SQL> ALTER TABLE test enable novalidate constra
ALTER TABLE test enable novalidate CONSTRAINT p
*
ERROR 位于第 1 行:
ORA-02437: 無法驗證 (DBO.PK_TEST) - 違反主鍵      

參考hrb_qiuyb文章

SQL> CREATE TABLE t(a INT,b varchar2(20));

TABLE created.

SQL> INSERT INTO t VALUES(1,'lijie');

1 ROW created.

SQL> ALTER TABLE t ADD constraints pk_t_a PRIMARY KEY(a);

TABLE altered.

SQL> SELECT constraint_name,constraint_type,STATUS FROM user_constraints
WHERE TABLE_NAME='T';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
PK_T_A                         P ENABLED

SQL> SELECT index_name,uniqueness FROM user_indexes WHERE TABLE_NAME='T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
PK_T_A                         UNIQUE

SQL> ALTER TABLE t disable constraints pk_t_a;

TABLE altered.

SQL> SELECT constraint_name,constraint_type,STATUS FROM user_constraints
WHERE TABLE_NAME='T';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
PK_T_A                         P DISABLED
SQL> SELECT index_name,uniqueness FROM user_indexes WHERE TABLE_NAME='T';

no ROWS selected

SQL> INSERT INTO t VALUES(1,'tina');

1 ROW created.

SQL> ALTER TABLE t enable novalidate constraints pk_t_a;
ALTER TABLE t enable novalidate constraints pk_t_a
*
ERROR at line 1:
ORA-02437: cannot validate (TAOBAO.PK_T_A) - PRIMARY KEY violated      

這裡問題是：primary key所依賴的唯一鍵索引起的.

解決方法：

SQL> CREATE TABLE t2 AS (SELECT * FROM t WHERE 1=0);

TABLE created.

SQL> CREATE INDEX ind_t2_a ON t2(a);

INDEX created.

SQL> INSERT INTO t2 VALUES(1,'lijie');

1 ROW created.

SQL> ALTER TABLE t2 ADD constraints pk_t2_a PRIMARY KEY(a);

TABLE altered.

SQL> SELECT constraint_name,constraint_type,STATUS FROM user_constraints
WHERE TABLE_NAME='T2';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
PK_T2_A                        P ENABLED

SQL> SELECT index_name,uniqueness FROM user_indexes WHERE TABLE_NAME='T2';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
IND_T2_A                       NONUNIQUE

SQL> ALTER TABLE t2 disable constraints pk_t2_a;

TABLE altered.

SQL> SELECT constraint_name,constraint_type,STATUS FROM user_constraints
WHERE TABLE_NAME='T2';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
PK_T2_A                        P DISABLED

SQL> SELECT index_name,uniqueness FROM user_indexes WHERE TABLE_NAME='T2';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
IND_T2_A                       NONUNIQUE

SQL> INSERT INTO t2 VALUES(1,'tina');

1 ROW created.

SQL> ALTER TABLE t2 enable validate  constraints pk_t2_a;
ALTER TABLE t2 enable validate  constraints pk_t2_a
*
ERROR at line 1:
ORA-02437: cannot validate (TAOBAO.PK_T2_A) - PRIMARY KEY violated
SQL> ALTER TABLE t2 enable novalidate constraints pk_t2_a;

TABLE altered.      

One Response to “關于限制ENABLE NOVALIDATE的一個疑問”