oracle字段重复新增错误,Oracle在重复列上创建主键/索引

组合特性说明

Validate

Novalidate

已有记录

新增/删除记录

已有记录

新增/删除记录

Enable

Yes

Yes

No

Yes

Disable

Yes

No

No

No

Validate确保已有数据符合约束；

Novalidate不必考虑已有数据是否符合约束。

除非Novalidate被指定，Enable默认Validate；

除非Validate被指定，Disable默认Novalidate。

Enable Validate与Enable相同，检查已有记录和新增记录，确保都符合约束；

Enable

Novalidate允许已有记录不必满足约束条件，但新增/修改的记录必须满足；

Disable Validate禁用约束，删除约束上的索引，不允许修改任何被约束的记录；

Disable Novalidate与Disable相同，禁用约束，删除约束上的索引，且允许修改被约束的记录。

创建测试表

SQL> create

table pk1(id number(2),name varchar2(8));

表已创建。

SQL> insert

into pk1 values(1,'abc');

已创建 1 行。

SQL> alter table pk1 add constraint pk_pk1

primary key(id); --设置主键约束。

表已更改。

SQL> create

table fr1(id number(2),name varchar2(8));

表已创建。

SQL> insert

into fr1 values(1,'abc');

已创建 1 行。

SQL> alter

table fr1 add constraint fk_fr1 foreign key(id) references

pk1(id);

表已更改。

--设置外键

测试外键下使用enable

novalidate/disable novalidate的情况

SQL> insert

into fr1 values(2,'aaa');

insert into

fr1 values(2,'aaa')

*

第 1 行出现错误:

ORA-02291: 违反完整约束条件 (SYS.FK_FR1) - 未找到父项关键字

SQL> alter

table fr1 disable novalidate constraint fk_fr1;

表已更改。

SQL> insert

into fr1 values(2,'aaa');

已创建 1 行。

SQL> select

* from fr1;

ID

NAME

----------

--------

1

abc

2

aaa

SQL> alter

table fr1 enable validate constraint fk_fr1;

alter table

fr1 enable validate constraint fk_fr1

*

第 1 行出现错误:

ORA-02298: 无法验证 (SYS.FK_FR1) - 未找到父项关键字

SQL> alter

table fr1 enable novalidate constraint fk_fr1;

表已更改。

--从上面测试结果可见enable novalidate和disable

novalidate在外键约束下使用是好用的。

测试主键下使用enable

novalidate/disable novalidate的情况

SQL> select

* from pk1;

ID

NAME

----------

--------

1

abc

SQL> alter

table pk1 disable novalidate primary key;

alter table

pk1 disable novalidate primary key

*

第 1 行出现错误:

ORA-02297: 无法禁用约束条件 (SYS.PK_PK1) - 存在相关性

SQL> alter

table fr1 disable validate constraint fk_fr1;

表已更改。

SQL> alter

table pk1 disable novalidate primary key;

表已更改。

SQL> insert

into pk1 values(1,'ccc');

已创建 1 行。

SQL> select

* from pk1;

ID

NAME

----------

--------

1

abc

1

ccc

SQL> alter

table pk1 enable novalidate primary key;

alter table

pk1 enable novalidate primary key

*

第 1 行出现错误:

ORA-02437: 无法验证 (SYS.PK_PK1) - 违反主键

SQL> select

INDEX_NAME,TABLE_NAME,TABLE_OWNER from user_indexes where

index_name

='PK_PK1';

未选定行

--主键对应的索引PK_PK1已经不在了。

SQL> create

index cc on pk1(id);

索引已创建。

SQL> alter

table pk1 enable novalidate primary key;

表已更改。

--OK了。

--从上面的测试结果看出：novalidate在非主键，唯一键时可以正常工作。在对主键，唯一键使用时需要先创建相关索引,再使用novalidate。Primary

and unique keys must use nonunique indexes。

举一个使用novalidate的案例：

给一个有数据的表增加一个非空的列。要求：需要在该表上增加一个字段status，但该字段不能为空，历史数据可以为空。最后将历史数据的该新加字段统一置为1

SQL> desc

pk1;

名称 是否为空? 类型

-----------------------------------------

-------- -------------

ID NOT NULL

NUMBER(2)

NAME

VARCHAR2(8)

SQL> select

* from pk1;

ID

NAME

----------

--------

1

abc

2

cba

SQL> alter

table pk1 add status number(1);

表已更改。

SQL> alter

table pk1 modify status not null enable novalidate;

表已更改。

SQL> select

* from pk1;

ID NAME

STATUS

----------

-------- ----------

1

abc

2

cba

SQL> update

pk1 set status=1;

已更新2行。

SQL>

commit;

提交完成。

SQL> select

* from pk1;

ID NAME

STATUS

----------

-------- ----------

1 abc

1

2 cba

1

SQL> insert

into pk1 values(3,'bca',null);

insert into

pk1 values(3,'bca',null)

*

第

1 行出现错误:

ORA-01400: 无法将 NULL 插入 ("SYS"."PK1"."STATUS")

除上之外还有一种简单的方法

先增加一列， alter table t add col

number;

update table t set

t.col=rownum;

然后alter

table t add contstraint pk_t primary key (col)