关于约束ENABLE NOVALIDATE的一个疑问

最新推荐文章于 2022-11-08 15:30:33 发布

weixin_30872733

最新推荐文章于 2022-11-08 15:30:33 发布

阅读量118

点赞数

文章标签：大数据数据库

原文链接：http://www.cnblogs.com/seasonzone/p/4229164.html

版权

http://www.dbunix.com/?p=188

关于约束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.