ORA-02437 使对已有数据不检查的主键约束生效

最新推荐文章于 2021-04-11 05:46:36 发布

blockheading

最新推荐文章于 2021-04-11 05:46:36 发布

阅读量1k

点赞数

分类专栏： Oracle oerr

本文链接：https://blog.csdn.net/u013169075/article/details/71906071

版权

Oracle oerr 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

--ORA-02437 使对已有数据不检查的主键约束生效
SYS@PROD1> desc t1
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 ID								NUMBER
 TEXT								VARCHAR2(2000)

SYS@PROD1> select * from t1;

	ID TEXT
---------- --------------------
	 1 a
	 2 b
	 
SYS@PROD1> alter table t1 add constraint t1_pk_1 primary key (id);

Table altered.

SYS@PROD1> select constraint_name,constraint_type,deferrable,deferred,status from user_constraints where table_name='T1';

CONSTRAINT_NAME 	       C DEFERRABLE	DEFERRED  STATUS
------------------------------ - -------------- --------- --------
T1_PK_1 		       P NOT DEFERRABLE IMMEDIATE ENABLED

SYS@PROD1> select index_name,uniqueness,index_type from user_indexes where table_name='T1';

INDEX_NAME		       UNIQUENES INDEX_TYPE
------------------------------ --------- ---------------------------
T1_PK_1 		       UNIQUE	 NORMAL

SYS@PROD1> insert into t1 values (1,'a');
insert into t1 values (1,'a')
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.T1_PK_1) violated


SYS@PROD1> alter table t1 modify constraint t1_pk_1 disable;

Table altered.

SYS@PROD1> select constraint_name,constraint_type,deferrable,deferred,status from user_constraints where table_name='T1';

CONSTRAINT_NAME 	       C DEFERRABLE	DEFERRED  STATUS
------------------------------ - -------------- --------- --------
T1_PK_1 		       P NOT DEFERRABLE IMMEDIATE DISABLED

SYS@PROD1> select index_name,uniqueness,index_type from user_indexes where table_name='T1';

no rows selected

SYS@PROD1> insert into t1 values (1,'a');

1 row created.

SYS@PROD1> alter table t1 modify constraint t1_pk_1 enable novalidate;  --直接修改无法通过
alter table t1 modify constraint t1_pk_1 enable novalidate
*
ERROR at line 1:
ORA-02437: cannot validate (SYS.T1_PK_1) - primary key violated


SYS@PROD1> alter table t1 drop constraint t1_pk_1;  --删除主键约束

Table altered.

SYS@PROD1> alter table t1 add constraint t1_pk_2 primary key (id) deferrable initially deferred;  --由于有重复值所以不能通过validate
alter table t1 add constraint t1_pk_2 primary key (id) deferrable initially deferred
                              *
ERROR at line 1:
ORA-02437: cannot validate (SYS.T1_PK_2) - primary key violated


SYS@PROD1> alter table t1 add constraint t1_pk_2 primary key (id) deferrable initially deferred novalidate;

Table altered.

SYS@PROD1> select constraint_name,constraint_type,deferrable,deferred,status from user_constraints where table_name='T1';

CONSTRAINT_NAME 	       C DEFERRABLE	DEFERRED  STATUS
------------------------------ - -------------- --------- --------
T1_PK_2 		       P DEFERRABLE	DEFERRED  ENABLED

SYS@PROD1> select index_name,uniqueness,index_type from user_indexes where table_name='T1';

INDEX_NAME		       UNIQUENES INDEX_TYPE
------------------------------ --------- ---------------------------
T1_PK_2 		       NONUNIQUE NORMAL

SYS@PROD1> select * from t1;

	ID TEXT
---------- --------------------
	 1 a
	 2 b
	 1 a
	 
SYS@PROD1> insert into t1 values (1,'c');  --检验DEFERRED效果

1 row created.

SYS@PROD1> commit
  2  ;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SYS.T1_PK_2) violated