cannot drop index
来源:互联网 发布:演唱会售票软件 编辑:程序博客网 时间:2024/06/05 03:06
SQL> drop index oos_index;
drop index oos_index
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
我们知道当创建Primary key和unique约束时,如果在该key上不存在索引,则Oracle会自动创建对应的unique索引,而当你要删除该索引时,必须先Disable或Drop该约束。看下面的例子:
SQL>CREATE TABLE employees
2 (
3 empno NUMBER(6) PRIMARY KEY,
4 name VARCHAR2(30),
5 dept_no NUMBER(2)
6 );
Table created.
SQL> select index_name,owner,table_NAME from all_indexes where owner=’SFA’ AND table_name=’EMPLOYEES’;
INDEX_NAME OWNER TABLE_NAME
———————- ——————- —————–
SYS_C007594 SFA EMPLOYEES
SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME=’EMPLOYEES’;
CONSTRAINT_NAME C TABLE_NAME INDEX_NAME
—————————— — ————————- ——————
SYS_C007594 P EMPLOYEES SYS_C007594
SQL> DROP INDEX SYS_C007594;
DROP INDEX SYS_C007594
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> ALTER TABLE employees MODIFY PRIMARY KEY DISABLE; ()
Table altered.
SQL> select index_name,owner,table_NAME from all_indexes where owner=’SFA’ AND table_name=’EMPLOYEES’;
no rows selected
SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME=’EMPLOYEES’;
CONSTRAINT_NAME C TABLE_NAME INDEX_NAME
—————————— — ————————- ——————
SYS_C007594 P EMPLOYEES SYS_C007594
SQL> ALTER TABLE employees
2 MODIFY PRIMARY KEY ENABLE;
Table altered.
SQL> select index_name,owner,table_NAME from all_indexes where owner=’SFA’ AND table_name=’EMPLOYEES’;
INDEX_NAME OWNER TABLE_NAME
——————- —————– ————-
SYS_C007594 SFA EMPLOYEES
从上面可以看出,如果创建了Primary Key约束,则Oracle会自动帮你创建相应的unique索引。当把Primary Key约束Disable时会自动删除对应的Unique索引,而重新将该约束Enable时,Oracle会重建
Unique索引。特别要注意:当Disable PK或Unique约束时,Oracle只会删除对应的Unique索引。可参考Oracle文档中的解释:
If you enable a unique or primary key constraint, and if no index exists on the key, Oracle creates a unique index. This index is dropped if the constraint is subsequently disabled, and Oracle rebuilds the index every time the constraint is enabled.
To avoid rebuilding the index and eliminate redundant indexes, create new primary key and unique constraints initially disabled. Then create (or use existing) nonunique indexes to enforce the constraint. Oracle does not drop a nonunique index when the constraint is disabled, so subsequent ENABLE operations are facilitated.
在Oracle 9i中,用于支持Primary Key和Unique Key约束的索引可独立于约束本身,实现方法是在CREATE TABLE或ALTER TABLE时指定USING INDEX子句,例子如下:
SQL> CREATE TABLE employees
2 (empno NUMBER(6),
3 name VARCHAR2(30),
4 dept_no NUMBER(2),
5 CONSTRAINT emp_pk PRIMARY KEY(empno)
6 USING INDEX
7 (CREATE INDEX emp_pk_idx ON employees(empno) TABLESPACE indx)
8 );
Table created.
SQL> select index_name,owner,table_NAME from all_indexes where owner=’SFA’ AND table_name=’EMPLOYEES’;
INDEX_NAME OWNER TABLE_NAME
————————— ————- ———————-
EMP_PK_IDX SFA EMPLOYEES
SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME=’EMPLOYEES’;
CONSTRAINT_NAME C TABLE_NAME INDEX_NAME
—————————— — ——————– —————
EMP_PK P EMPLOYEES EMP_PK_IDX
这样做的好处是:
1。可将索引存储在指定的表空间中,从而与表分离
2。通过创建一个非唯一索引,让PK或Unique Key使用,可避免在Enable或Disable PK或Unique Key时重建索引,同时可以消除多余的索引。
在删除约束时可选择保留索引:
1。ALTER TABLE employees DROP PRIMARY KEY KEEP INDEX;
2。ALTER TABLE employees DROP CONSTRAINT emp_pk;–对应的索引必须为非唯一索引
对于第二条语句,必须是对应的索引为非唯一索引,否则会连索引一并删除。
- cannot drop index
- ORA-02429: cannot drop index used for enforcement of unique/primary key
- ORA-02429: cannot drop index used for enforcement of unique/primary key
- 解决Cannot drop index 'PK_school_schooltype': needed in a foreign key constraint
- SQLite语法 DROP INDEX
- MYSQL DROP INDEX的BUG
- 12.2 使用DROP INDEX删除索引
- 12.2 使用DROP INDEX删除索引
- alter index rebuild 与 drop create index效率分析
- ora-28014:cannot drop administrative users
- org.activiti.engine.impl.db.DbSqlSession - problem during schema drop, statement drop index ACT_IDX_
- Oracle index rebuild online 与 rebuild 及 drop index 后重建
- ora-019401 cannot drop a user that is currently connected
- ORA-01940: cannot drop a user that is currently connected
- ORA-01940: cannot drop a user that is currently connected
- ORA-01940: cannot drop a user that is currently connected
- ORA-01940:cannot drop a user that is currently connected
- ORA-01940: cannot drop a user that is currently connected
- HDOJ 2084 数塔
- 设计模式之(一)工厂模式Factory
- java 读取propertites
- session页面传值
- poj 1701
- cannot drop index
- [技术分享] 20110803,Web 代理客户端通过 TMG ISA 不能访问新浪微博等网站
- 字符集问题研究
- JS 闭包应用
- 10个必需的iOS开发工具和资源
- jar包的作用
- Generator
- javascript calendar万年历使用实例
- C#中的触发事件