主键与索引的关系

在oracle中,我们创建一个主键,则同时自动创建了一个同名的唯一索引;删除主键,则主键约束和对应的唯一索引都删除了。这是我们经常见到的现象。


 

发出一个创建主键的sql,oracle其实执行了两步:创建主键约束、创建/关联 唯一索引。

步骤是这样的:

创建主键约束时,检查该主键字段上是否已经存在唯一索引。若不存在,则自动创建同名唯一索引;若存在,则直接创建主键约束,并将该约束和已经存在的唯一索引对应上。


删除主键约束时,可以决定是否保留对应的索引;删除唯一索引时,若存在对应的主键约束,则不能删除。


 

总之,存在主键约束,则肯定存在与之对应的唯一索引,而存在唯一索引,不一定对应着有主键约束。

 

下面我们验证一下:

SQL> create table test_pri(a number(1), b number(1));

表已创建。

--1.创建主键,则自动创建同名唯一索引

--1.1创建主键,主键约束和唯一索引同时创建

SQL> alter table test_pri add constraint pk_test_pri_a primary key(a);

表已更改。

SQL> select CONSTRAINT_NAME, TABLE_NAME, INDEX_NAME
2 from user_constraints where table_name = 'TEST_PRI';

CONSTRAINT_NAME   TABLE_NAME   INDEX_NAME  
----------------- ------------ -------------
PK_TEST_PRI_A     TEST_PRI     PK_TEST_PRI_A

SQL> select INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
2 from user_indexes where table_name = 'TEST_PRI';

INDEX_NAME       INDEX_TYPE   TABLE_NAME   UNIQUENES
---------------- ------------ ------------ ---------
PK_TEST_PRI_A    NORMAL       TEST_PRI     UNIQUE

--1.2 删除主键,主键约束和对应的唯一索引都删除了

SQL> alter table test_pri drop constraint pk_test_pri_a;

表已更改。

SQL> select CONSTRAINT_NAME, TABLE_NAME, INDEX_NAME
2 from user_constraints where table_name = 'TEST_PRI';

未选定行

SQL> select INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
2 from user_indexes where table_name = 'TEST_PRI';

未选定行

--1.3其实删除主键时可以选择保留索引的

SQL> alter table test_pri add constraint pk_test_pri_a primary key(a);

表已更改。

SQL> alter table test_pri drop constraint pk_test_pri_a keep index;

表已更改。

SQL> select CONSTRAINT_NAME, TABLE_NAME, INDEX_NAME
2 from user_constraints where table_name = 'TEST_PRI';

未选定行

SQL> select INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
2 from user_indexes where table_name = 'TEST_PRI';

INDEX_NAME      INDEX_TYPE TABLE_NAME UNIQUENES
--------------- ----------- ----------- ---------
PK_TEST_PRI_A   NORMAL      TEST_PRI    UNIQUE

 

--2.在存在唯一索引的列上创建主键,则只创建主键约束,同时将该约束与已有唯一索引关联上(名称可以不一致)

SQL> drop index pk_test_pri_a;

索引已丢弃。

--2.1 先创建唯一索引,再创建主键,名称可以不一致

SQL> create unique index pk_test_pri_a on test_pri(a);

索引已创建。

SQL> alter table test_pri add constraint pk_test_pri primary key(a);

表已更改。

SQL> select INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
2 from user_indexes where table_name = 'TEST_PRI';

INDEX_NAME      INDEX_TYPE TABLE_NAME   UNIQUENES
--------------- ----------- ------------ ---------
PK_TEST_PRI_A   NORMAL      TEST_PRI     UNIQUE

SQL> select CONSTRAINT_NAME, TABLE_NAME, INDEX_NAME
2 from user_constraints where table_name = 'TEST_PRI';

CONSTRAINT_NAME    TABLE_NAME   INDEX_NAME
------------------ ------------ -------------
PK_TEST_PRI        TEST_PRI     PK_TEST_PRI_A

--2.2 不可删除存在主键约束的唯一索引

SQL> drop index PK_TEST_PRI_A;
drop index PK_TEST_PRI_A
           *
ERROR 位于第 1 行:
ORA-02429: 无法删除用于强制唯一/主键的索引

--2.3虽然两者名称不一致,但也是关联在一块的:删除约束,则对应的索引同时删除
SQL> alter table test_pri drop constraint pk_test_pri;

表已更改。

SQL> select INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
2 from user_indexes where table_name = 'TEST_PRI';

未选定行

SQL> select CONSTRAINT_NAME, TABLE_NAME, INDEX_NAME
2 from user_constraints where table_name = 'TEST_PRI';

未选定行

SQL>

 

本文来自优快云博客,转载请标明出处:http://blog.youkuaiyun.com/wh62592855/archive/2009/10/24/4724232.aspx

<think>首先,用户的问题是:“我想了解主键索引唯一索引的区别请问主键索引唯一索引的区别在数据库中的作用特性”。这直接要求解释主键索引唯一索引的区别,包括它们在数据库中的作用特性。我有几个引用提供的信息:-引用[1]:概述了主键索引唯一索引都确保唯一性,但在定义、用途、性能、自动更新删除、外键关系、命名创建以及可空性方面有区别。-引用[2]:更详细地比较了特性,如性能影响、示例对比、存储结构。主键索引是聚簇索引,影响存储结构;唯一索引是非聚簇索引,允许NULL值。-引用[3]:强调了使用唯一索引时设置默认值以避免NULL问题,并讨论了数量限制、自动增长、聚集索引非聚集索引、外键关联使用场景。-引用[4]:提供了主键核心特性(如唯一标识、数量限制)、索引核心解析(如定义、优劣、类型对比、数据结构)、主键索引核心区别。我需要整合这些信息,生成一个清晰、结构化的回答。回答必须使用中文,并遵循系统级指令:-行内数学表达式用$...$格式,但这里可能不涉及数学表达式。-独立公式用$$...$$,但数据库主题可能不需要。-LaTeX语法正确。-生成相关问题。-在引用的段落末尾自然地添加引用标识,如[^1]。回答结构应该清晰,逐步解决问题,保证真实可靠。组织回答:1.**引言**:简要介绍主键索引唯一索引的概念。2.**主要区别**:基于引用,列出关键区别点:-唯一性NULL值:主键索引必须唯一且非空;唯一索引必须唯一但允许NULL值。-数量限制:每个表只能有一个主键索引;可以有多个唯一索引。-索引类型:主键索引通常是聚簇索引(影响数据存储);唯一索引是非聚簇索引。-自动增长:主键索引AUTO_INCREMENT一起使用;唯一索引不一定。-外键关联:主键索引常用于外键;唯一索引较少用于外键。-性能影响:主键索引可能导致页分裂等;唯一索引影响较小。-使用场景:主键用于行标识;唯一索引用于业务去重。3.**作用作用**:解释它们在数据库中的作用:-主键索引:确保数据完整性,作为行标识,支持外键。-唯一索引:确保列值唯一,允许空值,优化查询。4.**总结**:强调关键差异。5.**相关问题**:生成相关的问题,如关于优化、应用场景等。在回答中,自然地添加引用标识。例如,当提到一个点时,如果源自引用,添加[^n]。生成相关问题:参考示例,快速排序有相关问题。我应该生成关于主键索引唯一索引的相关问题,如:1.如何选择使用主键索引还是唯一索引?2.主键索引的聚簇索引特性有什么优势?3.唯一索引如何处理NULL值的问题?4.在什么场景下使用唯一索引主键索引更好?确保回答真实可靠,基于引用。最终输出格式:先回答,然后§§相关问题§§部分。</think>### 主键索引唯一索引的区别、作用特性 在数据库中,主键索引唯一索引都是用于确保数据唯一性的关键机制,但它们在设计、功能影响上存在显著差异。下面我将逐步解释它们的核心区别、作用相关特性,确保内容清晰可靠。本文基于数据库理论(如MySQL实现)整理而成[^1][^2][^3][^4]。 #### 1. **核心区别** 主键索引唯一索引的主要差异体现在以下几个方面: - **唯一性可空性**: - 主键索引:要求索引列的值**必须唯一且非空**(NULL值不允许)。这确保了每一行数据都有唯一的标识符,例如在用户表中用`user_id`作为主键[^1][^3]。 - 唯一索引:要求索引列的值**必须唯一,但允许NULL值**(一个表可以有多个NULL值,因为NULL不被视为重复)。例如,在邮箱字段上设置唯一索引,允许未填写邮箱的用户存在[^2][^3]。 - **数量限制**: - 主键索引:每个表**只能有一个**主键索引。它是表的“核心标识符”[^1][^4]。 - 唯一索引:每个表**可以有多个**唯一索引,应用于不同列或列组合。例如,一个商品表可以在`product_code``serial_number`上分别创建唯一索引[^2][^4]。 - **索引类型存储结构**: - 主键索引:通常是**聚簇索引**(Clustered Index)。这意味着数据行的物理存储顺序主键的逻辑顺序一致(如B+树结构),查询时能直接访问数据,提高效率。但插入或更新操作可能导致页分裂(数据重组),影响性能[^2][^3][^4]。 - 唯一索引:通常是**非聚簇索引**(Non-clustered Index)。索引条目存储主键引用而非数据本身,查询时需要二次查找(回表操作),影响速度但占用空间较小,对更新操作的影响也较低[^2][^3][^4]。 - **自动增长外键关联**: - 主键索引:常**自动增长属性**(如`AUTO_INCREMENT`)结合,确保主键值连续递增。它还常用于**外键约束**,作为其他表的引用点,维护数据完整性(例如订单表引用用户表的主键)[^3][^4]。 - 唯一索引:**不支持自动增长**,仅关注值唯一性。它较少用于外键,更多用于业务逻辑的去重需求(如防止重复邮箱注册)[^1][^3]。 - **性能影响**: - 主键索引:作为聚簇索引,对**查询性能提升显著**(尤其范围查询),但可能增加插入/更新的开销(如页分裂)。在数据量大时,维护成本较高[^2][^4]。 - 唯一索引:对查询优化有效,但需二次查找;对写操作影响较小,适合高频更新的列[^2][^4]。 #### 2. **在数据库中的作用** - **主键索引的作用**: - **行标识数据完整性**:唯一标识每一行数据,确保没有重复或空值,是数据模型的基石。例如,在关系型数据库中,主键是连接表的外键基础[^3][^4]。 - **查询优化**:作为默认聚簇索引,能加速基于主键的检索(如`SELECT * FROM table WHERE id=1`),减少I/O操作[^4]。 - **自动维护**:结合自动增长,简化新数据插入,避免手动管理唯一值[^3]。 - **唯一索引的作用**: - **业务约束**:保证特定列的唯一性(如用户名、手机号),支持业务规则(如“一用户一邮箱”),但不强制非空,灵活性高[^1][^2]。 - **查询辅助**:为非主键列提供快速查找,减少全表扫描。例如,在`email`列上创建唯一索引可优化登录验证查询[^4]。 - **空间效率**:作为非聚簇索引,占用额外空间较小,适合多列约束场景[^2]。 #### 3. **关键特性总结** | 特性 | 主键索引 | 唯一索引 | |---------------------|------------------------------|------------------------------| | **唯一性** | 必须唯一且非NULL | 必须唯一但允许NULL | | **数量限制** | 每表仅一个 | 每表可多个 | | **索引类型** | 聚簇索引(数据存储有序) | 非聚簇索引(存储主键引用) | | **自动增长** | 常支持(如AUTO_INCREMENT) | 不支持 | | **外键关联** | 常用作外键引用 | 较少用于外键 | | **NULL值处理** | 禁止NULL | 允许NULL(需注意唯一性问题)[^3] | | **性能影响** | 查询快,但写操作开销大 | 查询需二次查找,写操作影响小 | | **典型使用场景** | 核心行标识(如ID字段) | 业务去重(如唯一邮箱、编码) | 在实际应用中,选择主键索引还是唯一索引取决于业务需求:主键索引强调数据完整性行标识,适合主表;唯一索引更灵活,用于辅助约束[^1][^4]。需注意,唯一索引的NULL值可能导致查询歧义(如`WHERE email IS NULL`返回多行),建议设置默认值避免问题[^3]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值