ORA-02437: cannot validate primary key violated

本次测试主要测试主键的添加、主键添加之后index的信息、以及构造一个已经有重复数据,如何添加主键的场景。

1.创建测试表

create table supplier
(
supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id,supplier_name)
)

SQL> desc supplier;
 Name                               Null?    Type
 --------------------------------- -------- --------------------------------------------
 SUPPLIER_ID                       NOT NULL NUMBER(10)
 SUPPLIER_NAME                     NOT NULL VARCHAR2(50)
 CONTACT_NAME                               VARCHAR2(50)

//然后查看主键约束的信息,以及索引所在列。

SQL> select constraint_type,constraint_name,status from user_constraints where table_name='SUPPLIER';

C CONSTRAINT_NAME                STATUS
- ------------------------------ --------
C SYS_C0028660                   ENABLED
C SYS_C0028661                   ENABLED
P SUPPLIER_PK                    ENABLED

主键类型为P的约束supplier_pk即为在表创建语句时,一同增加的约束,状态为enable,类型为P

由于这个primary key在两个column上,分别为:supplier_id,supplier_name

//由于是primary key,所以会自动创建一个normal类型的,唯一索引(确保唯一性)。而这个唯一索引横跨在两个column上:supplier_id,supplier_name

SQL> select index_name,INDEX_TYPE,UNIQUENESS from user_indexes where table_name='SUPPLIER';

INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
SUPPLIER_PK                    NORMAL                      UNIQUE

SQL> select column_name,index_name from user_ind_columns where table_name='SUPPLIER';

COLUMN_NAME                              INDEX_NAME
---------------------------------------- ------------------------------
SUPPLIER_ID                              SUPPLIER_PK
SUPPLIER_NAME                            SUPPLIER_PK

2.下面开始干掉primary key,然后向表中插入一些重复数据,然后再尝试修复、重新添加主键。

SQL> alter table supplier drop CONSTRAINT supplier_pk;

Table altered.

SQL> select constraint_type,constraint_name,status from user_constraints where table_name='SUPPLIER';

C CONSTRAINT_NAME                STATUS
- ------------------------------ --------
C SYS_C0028660                   ENABLED
C SYS_C0028661                   ENABLED

SQL> select index_name,INDEX_TYPE,UNIQUENESS from user_indexes where table_name='SUPPLIER';

no rows selected

//主键的约束被干掉了,也就是primary key被干掉了。只剩下两个Check类型(check not null)的约束,索引也没有了。

3.插入重复数据。

insert into supplier values (1,'UPS','zhangsan')
insert into supplier values (2,'EMS','lisi')
insert into supplier values (2,'EMS','lisi')

SQL> select * from supplier;

SUPPLIER_ID SUPPLIER_NAME                  CONTACT_NAME
----------- ------------------------------ ------------------------------
          1 UPS                            zhangsan
          2 EMS                            lisi
          2 EMS                            lisi

4.尝试修复:增加primary key

ALTER TABLE supplier add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id,supplier_name);

SQL> ALTER TABLE supplier add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id,supplier_name);
ALTER TABLE supplier add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id,supplier_name)
                                    *
ERROR at line 1:
ORA-02437: cannot validate (CDWBASE.SUPPLIER_PK) - primary key violated

//报错了,很明显是由于有重复值的问题。如果是在现有的情况下重建pk,而且是表中有重复数据的情况下,可以借鉴enable novalidate。

//但是,由于处理的是pk,需要首先给相应的column添加index,添加一个normal、非唯一的index。如果添加一个唯一的index,那由于
//有重复数据,那么这个问题还是无解。

SQL> CREATE INDEX supplier_ind on SUPPLIER (supplier_id,supplier_name);

Index created.

SQL> select index_name,INDEX_TYPE,UNIQUENESS from user_indexes where table_name='SUPPLIER';

INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
SUPPLIER_IND                   NORMAL                      NONUNIQUE

SQL> SELECT COLUMN_NAME,INDEX_NAME FROM USER_IND_COLUMNS WHERE TABLE_NAME='SUPPLIER';

COLUMN_NAME                              INDEX_NAME
---------------------------------------- ------------------------------
SUPPLIER_ID                              SUPPLIER_IND
SUPPLIER_NAME                            SUPPLIER_IND

ALTER TABLE SUPPLIER add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id,supplier_name) using index enable novalidate;

SQL> ALTER TABLE SUPPLIER add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id,supplier_name) using index enable novalidate;

Table altered.

SQL> select constraint_type,constraint_name,status from user_constraints where table_name='SUPPLIER';

C CONSTRAINT_NAME                STATUS
- ------------------------------ --------
C SYS_C0028660                   ENABLED
C SYS_C0028661                   ENABLED
P SUPPLIER_PK                    ENABLED

SQL> select index_name,INDEX_TYPE,UNIQUENESS from user_indexes where table_name='SUPPLIER';

INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
SUPPLIER_IND                   NORMAL                      NONUNIQUE

SQL> SELECT COLUMN_NAME,INDEX_NAME FROM USER_IND_COLUMNS WHERE TABLE_NAME='SUPPLIER';

COLUMN_NAME                              INDEX_NAME
---------------------------------------- ------------------------------
SUPPLIER_ID                              SUPPLIER_IND
SUPPLIER_NAME                            SUPPLIER_IND

//可见,相应的pk添加上了。这个问题,如果在不想处理表内重复数据的情况下,只能通过非唯一的索引来解决。否则,则首先清理重复数据。
//enable novalidate的作用就是对于已经存在的数据,就不计较其一致性了,主要针对后续插入的数据。

SQL> insert into supplier values (2,'EMS','lisi');
insert into supplier values (2,'EMS','lisi')
*
ERROR at line 1:
ORA-00001: unique constraint (CDWBASE.SUPPLIER_PK) violated

–EOF–