删除主键的时候，索引是否删除

最新推荐文章于 2024-09-17 22:31:36 发布
最新推荐文章于 2024-09-17 22:31:36 发布
阅读量2.1k 收藏 6
点赞数 2
分类专栏： oracle

问题：删除主键时是否会同时自动删除索引？
答案：是否删除索引取决于索引是创建主键时自动创建的，还是创建主键前手工创建的。
如果期望删除主键时，同时删除索引，安全的做法是增加drop index选项。另外，如果为了防止因存在外键引用而删除失败，可以增加cascade选项。
以下内容在PLSQLDeveloper中亲测，为了代码便于阅读放到eclipse中做了格式调整。
测试无drop index/keepindex选项时的情况
手工创建索引，后增加主键
–建表
SQL> drop table test;
drop table test
ORA-00942: 表或视图不存在
SQL> create table test(ID INTEGER not null);
Table created
–建主键
SQL> create unique index PK_TEST on TEST(ID);
Index created
SQL> alter table TEST
2 add CONSTRAINT PK_TEST PRIMARY KEY (ID) using index;
Table altered
–查看索引
SQL> select index_name from user_indexes where index_name=’PK_TEST’;

INDEX_NAME

PK_TEST
–删除主键
SQL> alter table TEST drop primary key;
Table altered
–再查看索引，没有被删掉
SQL> select index_name from user_indexes where index_name=’PK_TEST’;

INDEX_NAME

PK_TEST
增加主键，自动创建索引
–建表
SQL> drop table test;
Table dropped
SQL> create table test(ID INTEGER not null);
Table created
–添加主键，自动创建索引
SQL> alter table TEST
2 add CONSTRAINT PK_TEST PRIMARY KEY (ID) using index;
Table altered
–查看索引
SQL> select index_name from user_indexes where index_name=’PK_TEST’;

INDEX_NAME

PK_TEST
–删除主键
SQL> alter table TEST drop primary key;
Table altered
–再次查看索引，已经删除
SQL> select index_name from user_indexes where index_name=’PK_TEST’;

INDEX_NAME

测试drop index选项时的情况
手工创建索引,后增加主键
–建表
SQL> drop table test;
Table dropped
SQL> create table test(ID INTEGER not null);
Table created
–建主键
SQL> create unique index PK_TEST on TEST(ID);
Index created
SQL> alter table TEST
2 add CONSTRAINT PK_TEST PRIMARY KEY (ID) using index;
Table altered
–查看索引
SQL> select index_name from user_indexes where index_name=’PK_TEST’;

INDEX_NAME

PK_TEST
–删除主键
SQL> alter table TEST drop primary key drop index;
Table altered
–再次查看索引，已经被删除
SQL> select index_name from user_indexes where index_name=’PK_TEST’;

INDEX_NAME

增加主键,自动创建索引
–建表
SQL> drop table test;
Table dropped
SQL> create table test(ID INTEGER not null);
Table created
–建主键
SQL> alter table TEST
2 add CONSTRAINT PK_TEST PRIMARY KEY (ID) using index;
Table altered
–查看索引
SQL> select index_name from user_indexes where index_name=’PK_TEST’;

INDEX_NAME

PK_TEST
–删除主键
SQL> alter table TEST drop primary key drop index;
Table altered
–再次查看索引，已经被删除
SQL> select index_name from user_indexes where index_name=’PK_TEST’;

INDEX_NAME

测试keep index选项时的情况
手工创建索引,后增加主键
–建表
SQL> drop table test;
Table dropped
SQL> create table test(ID INTEGER not null);
Table created
–建主键
SQL> create unique index PK_TEST on TEST(ID);
Index created
SQL> alter table TEST
2 add CONSTRAINT PK_TEST PRIMARY KEY (ID) using index;
Table altered
–查看索引
SQL> select index_name from user_indexes where index_name=’PK_TEST’;

INDEX_NAME

PK_TEST
SQL> –删除主键
SQL> alter table TEST drop primary key keep index;
Table altered
–再次查看索引，被保留
SQL> select index_name from user_indexes where index_name=’PK_TEST’;

INDEX_NAME

PK_TEST
增加主键,自动创建索引
–建表
SQL> drop table test;
Table dropped
SQL> create table test(ID INTEGER not null);
Table created
–建主键
SQL> alter table TEST
2 add CONSTRAINT PK_TEST PRIMARY KEY (ID) using index;
Table altered
–查看索引
SQL> select index_name from user_indexes where index_name=’PK_TEST’;

INDEX_NAME

PK_TEST
–删除主键
SQL> alter table TEST drop primary key keep index;
Table altered
–再次查看索引，依然被保留
SQL> select index_name from user_indexes where index_name=’PK_TEST’;

INDEX_NAME

PK_TEST
测试增加drop index选项时使用cascade关键字的作用
–新建table1
SQL> create table table1(ID INTEGER not null);
Table created
–新增主键约束
SQL> alter table table1 add CONSTRAINT PK_TEST PRIMARY KEY (ID) using index;
Table altered
–插入三个值1，2，3
SQL> insert into table1 values(1);
1 row inserted
SQL> insert into table1 values(2);
1 row inserted
SQL> insert into table1 values(3);
1 row inserted
SQL> select * from table1;

ID

                                  1
                                  2
                                  3

–新建table2
SQL> create table table2(ID1 INTEGER not null,ID2 INTEGER not null);
Table created
–新增table2主键约束
SQL> alter table table2 add constraint pk_table2 primary key (ID1) using index;
Table altered
–新增table2外键约束，其id2这一列指向table的id
SQL> alter table table2 add constraint fk_id2 foreign key (ID2) references table1(id);
Table altered
–向table2插入三行值(1,1),(2,2),(3,3)
SQL> insert into table2 values (1,1);
1 row inserted
SQL> insert into table2 values (2,2);
1 row inserted
SQL> insert into table2 values (3,3);
1 row inserted
SQL> select * from table2;

                                ID1                                     ID2

                                  1                                       1
                                  2                                       2
                                  3                                       3

–查看table2的约束，可以看到有主键和外键
SQL> select * from user_constraints a where a.table_name=’TABLE2’;

OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED

VOUDATA SYS_C0097617 C TABLE2 “ID1” IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2014/6/10 1
VOUDATA SYS_C0097618 C TABLE2 “ID2” IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2014/6/10 1
VOUDATA PK_TABLE2 P TABLE2 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014/6/10 1 VOUDATA PK_TABLE2
VOUDATA FK_ID2 R TABLE2 VOUDATA PK_TEST NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014/6/10 1
–删除table1主键，不使用cascade关键字，此时报错
SQL> alter table table1 drop primary key drop index;

alter table table1 drop primary key drop index

ORA-02273: 此唯一/主键已被某些外键引用

–删除table1主键，使用cascade关键字，不报错
SQL> alter table table1 drop primary key cascade drop index;

Table altered

–查看table2的约束，此时外键已经被干掉了
SQL> select * from user_constraints a where a.table_name=’TABLE2’;

OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED

VOUDATA SYS_C0097617 C TABLE2 “ID1” IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2014/6/10 1
VOUDATA SYS_C0097618 C TABLE2 “ID2” IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2014/6/10 1
VOUDATA PK_TABLE2 P TABLE2 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014/6/10 1 VOUDATA PK_TABLE2
–查看table2的数据，没有变化，说明只是外键关联去掉了，引用table1的数据保留
SQL> select * from table2;

                                ID1                                     ID2

                                  1                                       1
                                  2                                       2
                                  3                                       3

由此可见，若主键被其他表引用做外键，删除主键并drop index，若不使用cascade关键字则执行会报错，而使用cascade关键字会将其关联的外键同时删除。所在这种情况下cascade关键字还是谨慎使用，在完全考虑到了外键关联不需要时再使用，否则可能在未知的情况下将某些外键关联删除并且在调整过主键后忘记重新增加外键，执行时报错总好过不知情的情况下少了外键关联。

阿凡T
关注
专栏目录
mysql删除数据对索引的影响_索引对数据库删改查的性能影响
weixin_34208400的博客
02-03 2975
前置知识数据库索引数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引键值的逻辑顺序与索引所服务的表中相应行的物理顺序相同的索引，被称为聚集索引，反之为非聚集索引，索引一般使用二叉树排序索引键值的，聚集索引的索引值是直接指向数据表对应元组的，而非聚集索引的索引值仍会指向下一个索引数据块，并不直接指向元组，因为还有一层索引进行重定向，所以非聚集索引可以拥有不同的...
删除主键约束时是否删除索引
heqiyu34的专栏
12-04 1824
删除主键时是否会删除索引？ 答案取决于索引是创建主键时自动创建的，还是创建主键前手工创建的。 测试如下： --建表 create table hqy_test(id integer) ;   --建索引 create (unique) index idx_hqy_id on hqy_test(id) ;   --加主键 alter table hqy_test add const
删除主键导致使用普通索引失效（覆盖索引失效） 干货篇
u013469501的博客
07-21 518
本文用实验验证删除主键导致普通索引失效。 1、查看实验表结构及当前数据行数： 2、监控实验表当前索引使用情况： 3、走普通索引查询： 可以看到，索引modelid计数加1： 4、走覆盖索引查询： 可以看到，索引modelid计数再次加1： 5、删除主键，观察索引使用情况变化： 主键 删除共用26秒多，删除的过程中，索引监控视图中会显示一个时刻在滚动的数字，这个索引没有名字，但经过多次测试发现数字的最大值就是表的总行数（），而后消失，最终...
数据库中drop、delete 与truncate 的区别
weixin_51704238的博客
09-17 411
提示：本文主要讲的是mysql和sqlserver中三者的用法。
主键索引的创建、删除
weixin_45847088的博客
07-05 3534
表结构:(以id作为主键示范) CREATE TABLE order1( id INT , `name` VARCHAR(32), `uname` VARCHAR(32), `num` INT ) 主键索引创建: 方法一:建表时在表内设置，在int后添加primary key CREATE TABLE order1( id INT primary key, `name` VARCHAR(32), `uname` VARCHAR(32), ...
oracle根据索引名删除索引,Oracle删除主键保留索引的方法
weixin_31891047的博客
04-02 510
yangtingkun的"如何判断索引是系统产生还是用户创建的"http://yangtingkun.itpub.net/post/468/160390中说到：对于主键和唯一约束，如果没有事先建立索引的话，Oracle在创建的过程中会自动建立一个唯一索引。同样，Oracle在删除主键和约束的时候也会自动删除这个唯一索引今天正好有张大表要进行改名，按照要求，PK约束的名称也需要跟着改变，但是Orac...
MySQL 主键与索引的联系与区别分析
01-19
关系数据库依赖于主键，它是数据库物理...因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。数
Oracle删除主键约束的同时删除索引
wuxun1997的专栏
10-10 733
　　继续昨天的折腾(Oracle修改主键约束)，删掉主键约束后，发现唯一索引并未删掉。仔细看了下，主键约束跟唯一索引名称不一样，这说明是先创建了唯一索引，后创建的主键约束。我们来试验下： SQL> create unique index PK_T_INVITEE_RECORD_TEST on T_INVITEE_RECORD_TEST(INVITEEMSISDN, INVITERMSIS...
oracle删除表空间出现”无法删除用于强制唯一主键的索引“解决方法[参照].pdf
10-11
Oracle 删除表空间出现“无法删除用于强制唯一主键的索引”解决方法 在 Oracle 数据库中，删除表空间时可能会出现“无法删除用于强制唯一主键的索引”的错误。这种情况下，无法删除表空间，需要找到解决方法。在...
mysql sql删除主键索引_SQL创建索引和删除索引
weixin_32098487的博客
01-21 1625
1.使用CREATE 语句创建索引CREATE INDEX index_name ON table_name(column_name,column_name) include(score)普通索引CREATE UNIQUE INDEX index_name ON table_name (column_name) ;非空索引CREATE PRIMARY KEY INDEX index_name ON...
删除主键索引 oracle,删除主键无法删除对应索引问题 drop constraint
weixin_35744849的博客
04-05 1371
--在删除一个表主键的时候索引没有删掉的问题，如果主键索引是和主键约束一起建的，则删除约束的时候索引会自动删除掉，如果是先建了索引，然后建立主键，则删除约束的时候索引不会一起被删除掉测试：--创建测试表create table dbmgr.test_pk as select * from REINSDATA.REINS_PROP_PLAN_ADJ where rownum <1000--创建...
mysql sql删除主键索引_sql操作之添加删除索引
weixin_33211213的博客
01-27 2402
一、创建删除主建索引1.在创建表时就创建好索引CREATE TABLE `student` (`id` int(4) NOT NULL AUTO_INCREMENT,`name` char(20) NOT NULL,`age` tinyint(2) NOT NULL DEFAULT '0',`dept` varchar(16) DEFAULT NULL,PRIMARY KEY (`id`),KEY...
删除主键，唯一约束，什么时候会自动删除列上的索引？
cotchte0421的博客
07-11 1450
你可能经常会有这样的顾虑，在删除唯一约束或者主键约束的时候，附带的索引会不会被删除掉？现在的团队有一个规范，但凡是增加主键，都需要先手工创建索引，再增加主键。给出的原因是：这样删除主键的时候，索引就不会被删除掉了。Oracle是...
如何删除表的主键定义和索引定义呢？
xiaoj13的专栏
10-15 3389
找到方法了： sp_helpindex objname 一下子就看出了主键和索引的名字 删除主键用 alter table drop constraint PK_name 删除索引用 drop index tablename.indexname
MySQL添加/删除主键、外键、唯一键、索引、自增
热门推荐
生而为人我很抱歉
10-22 5万+
建表是添加外键create table tableName1( id int primary key, name varchar(128) default null, tableName2_id int not null, foreign key(tableName2_id) references tableName2(id) );单独添加外键alter table tableName1 add c
oracle数据库如何创建、查看、删除主键索引
m0_58193658的博客
08-09 343
--创建表 create table TFCONFIG( TYPE_ID VARCHAR2(20) not null, PROVINCE_CODE VARCHAR2(4) not null, PARAMETER_TYPE VARCHAR2(2) not null, LIMIT_NUM VARCHAR2(4) not null, EFFECTIVE_FALG VARCHAR2(2) default '1', UPDATE
【MySQL入门·索引】13.3 删除索引
Python老吕的博客
05-13 2223
通过本文的讨论，我们深入了解了MySQL中删除索引的方法、注意事项以及索引的维护、选择与监控等方面的知识。在实际应用中，我们需要根据具体的业务需求和数据访问模式来合理设计和管理索引。定期审查现有索引的使用情况，删除不再需要的索引以释放存储空间和提高性能。在创建新索引时，要考虑列的选择性、查询的WHERE子句以及避免过度索引等因素。使用复合索引来优化涉及多个列的查询条件。利用执行计划、慢查询日志和性能监控工具来分析和优化索引的使用情况。
索引分类|索引创建、删除|索引设计原则
NorthSmile的博客
07-10 1916
MySQL常见索引分类、创建删除索引，索引设计的原则
mysql 删除主键索引 语句
最新发布
10-18
MySQL中删除主键索引通常涉及两个步骤：首先，如果你想移除表中的主键约束，可以使用ALTER TABLE语句；其次，如果已经删除了主键字段，需要更新数据库模式使其不再依赖主键。 1. **删除主键约束**: ```sql ALTER ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值