oracle删除pk,【PK】Oracle 10g删除主键约束后无法删除唯一约束索引问题的模拟与分析...

【PK】Oracle 10g删除主键约束后无法删除唯一约束索引问题的模拟与分析

引自：http://space.itpub.net/519536/viewspace-628313

当先创建唯一约束后再创建主键约束的情况下，如果使用普通方法删除主键约束后，唯一约束索引不会被删除，这是Oracle 10g的一个PROBLEM。

本文通过一个实验给大家演示一下这个问题的出现过程及处理方法。

【问题现象】

在10g环境下，在删除主键约束后，在插入重复数据时候仍然报“ORA-00001: unique constraint (SEC.PK_T) violated”错误。

现象是主键约束已经删除成功，但是唯一约束索引没有级联删除。

【问题模拟】

1.创建表T

sec@ora10g> create table t (x int, y int);

Table created.

2.先创建惟一约束索引

sec@ora10g> create unique index pk_t on t (x);

Index created.

3.再创建主键约束

sec@ora10g> alter table t add (constraint pk_t primary key(x));

Table altered.

4.查看约束信息

sec@ora10g> col OWNER for a5

sec@ora10g> col CONSTRAINT_NAME for a30

sec@ora10g> col TABLE_NAME for a10

sec@ora10g> col INDEX_OWNER for a12

sec@ora10g> col INDEX_NAME for a10

sec@ora10g> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME from user_constraints where table_name = 'T';

OWNER CONSTRAINT_NAME                C TABLE_NAME INDEX_OWNER  INDEX_NAME

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

SEC   PK_T                           P T          SEC          PK_T

5.查看索引信息

sec@ora10g> select INDEX_NAME,INDEX_TYPE,GENERATED from user_indexes;

INDEX_NAME INDEX_TYPE                  G

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

PK_T       NORMAL                      N

GENERATED字段说明：

GENERATED     VARCHAR2(1)           Indicates whether the name of the index is system generated (Y) or not (N)

6.删除主键约束

sec@ora10g> alter table t drop constraint pk_t cascade;

Table altered.

7.确认约束和索引删除情况

sec@ora10g> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME from user_constraints where table_name = 'T';

no rows selected

sec@ora10g> select INDEX_NAME,INDEX_TYPE,GENERATED from user_indexes;

INDEX_NAME INDEX_TYPE                  G

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

PK_T       NORMAL                      N

可见，此时索引没有被删除。

因此，此时如果插入重复的数据，还是会报违反“约束”

sec@ora10g> insert into t values (1,1);

1 row created.

sec@ora10g> insert into t values (1,1);

insert into t values (1,1)

*

ERROR at line 1:

ORA-00001: unique constraint (SEC.PK_T) violated

【问题原因】

这个问题的原因可以参考MOS的[ID 309821.1]文章，文章中给出了具体原因。

Oracle 10G Does not Drop User Index Associated With Unique/Primary Key Constraints [ID 309821.1]

原来是Oracle在的10g版本中对内部函数"atbdui"进行了调整，导致在删除约束的时候无法删除用户创建的索引。

这个现象被Oracle分类到了“PROBLEM”。

【处理方法】

在删除约束的时候需要显示的指定“drop index”选项来完成索引的级链删除。

sec@ora10g> alter table t drop constraint pk_t cascade drop index;

Table altered.

经确认，约束和索引均被删除后，尝试插入两条重复数据，成功。

sec@ora10g> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME from user_constraints where table_name = 'T';

no rows selected

sec@ora10g> select INDEX_NAME,INDEX_TYPE,GENERATED from user_indexes;

no rows selected

sec@ora10g> insert into t values (1,1);

1 row created.

sec@ora10g> insert into t values (1,1);

1 row created.

【BTW】如果之前创建的是普通索引，即使使用“drop index”选项，该索引也不会被删除掉，实验参考如下：

sec@ora10g> drop table t purge;

Table dropped.

sec@ora10g> create table t (x int, y int);

Table created.

sec@ora10g> create index pk_t on t (x);

Index created.

sec@ora10g> alter table t add (constraint pk_t primary key(x));

Table altered.

sec@ora10g> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME from user_constraints where table_name = 'T';

OWNER CONSTRAINT_NAME                C TABLE_NAME INDEX_OWNER  INDEX_NAME

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

SEC   PK_T                           P T          SEC          PK_T

sec@ora10g> alter table t drop constraint pk_t cascade;

Table altered.

sec@ora10g> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME from user_constraints where table_name = 'T';

no rows selected

sec@ora10g> select INDEX_NAME,INDEX_TYPE,GENERATED from user_indexes;

INDEX_NAME INDEX_TYPE                  G

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

PK_T       NORMAL                      N

sec@ora10g> insert into t values (1,1);

1 row created.

sec@ora10g> insert into t values (1,1);

1 row created.

【小结】

实验是答疑解惑的重要手段。当遇到一个问题后，我们应该以一种科学严谨的态度对待之，必有所获。

Good luck.

secooler

10.03.01

-- The End --