对含有数据的表添加新约束时oracle对约束的有效性检查

David_k

oracle version 11.2.0.3.0 - 64bit

需要对含有数据的表添加一个唯一约束，由于历史数据比较混乱，少量数据的字段值相同，但是无法处理，
对新保存的记录要做唯一约束，选择 enable novalidate 对已经存在的数据不做检查，但实际却提示： 找到重复关键字。
以下下为实验步骤：

建立测试表
create table tt tablespace users as select object_id, object_name from user_objects where rownum < 10;

insert into tt select object_id, object_name from user_objects where rownum < 5;
commit;

查看有重复数据
select * from tt order by object_id;

添加约束失败
alter table tt add constraint idx_un_id unique(object_id)  enable novalidate ;
报错 ora-02299  找到重复关键字

继续
alter table tt add constraint idx_un_id unique(object_id)  disable novalidate ;  
添加成功，但是不符合要求，还能继续插入重复数据
insert into tt select object_id, object_name from user_objects where rownum < 5;
修改
alter table tt modify constraint idx_un_id enable  novalidate ;
报错  ora-02299  找到重复关键字

jason_yh

在创建约束之前先建一个索引就可以的
SQL> create index idx_id on tt(object_id);

Index created.

SQL> alter table tt add constraint idx__id unique(object_id)  enable novalidate;

Table altered.

SQL> insert into tt select object_id, object_name from user_objects where rownum < 5;
insert into tt select object_id, object_name from user_objects where rownum < 5
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.IDX__ID) violated

SQL> select * from tt;

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
        20 ICOL$
        44 I_USER1
        28 CON$
        15 UNDO$
        29 C_COBJ#
         3 I_OBJ#
        25 PROXY_ROLE_DATA$
        39 I_IND1
        51 I_CDEF2
        20 ICOL$
        44 I_USER1

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
        28 CON$
        15 UNDO$

David_k

楼上正解，感谢 jason_yh 的回复！

看来唯一约束一定要有个索引段来支撑，学习了