主键、唯一键与唯一索引的区别

本文探讨了数据库中主键约束、唯一键约束和唯一索引的区别,包括它们如何影响数据完整性、列值非空性及索引创建等方面。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

原文地址:http://blog.sina.com.cn/s/blog_82ee2ee60100xwl4.html

这篇文章的意义已经理解,但是他举得例子不错,保存下来,下次想看的时候可以参考一下。

 

一般,我们看到术语“索引”和“键”交换使用,但实际上这两个是不同的。索引是存储在数据库中的一个物理结构,键纯粹是一个逻辑概念。键代表创建来实施业务规则的完整性约束。索引和键的混淆通常是由于数据库使用索引来实施完整性约束。

 

接下来我们看看数据库中的主键约束、唯一键约束和唯一索引的区别。

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

 

SQL> create table test (          

  id int,

  name varchar2(20),

  constraint pk_test primary key(id))

  tablespace users;

 

Table created.

 

SQL> select constraint_name, constraint_type from user_constraints;

 

CONSTRAINT_NAME                C

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

PK_TEST                        P

 

在test表中,我们指定了ID列作为主键,Oracle数据库会自动创建一个同名的唯一索引:

SQL> select index_name, index_type, uniqueness, tablespace_name

  from user_indexes

  where table_owner='SCOTT'

  and table_name = 'TEST';

 

INDEX_NAME           INDEX_TYPE           UNIQUENES TABLESPACE_NAME

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

PK_TEST              NORMAL               UNIQUE    USERS

 

此时,如果我们再试图在ID列上创建一个唯一索引,Oracle会报错,因为该列上已经存在一个唯一索引:

SQL> create unique index idx_test_uk on test(id);

create unique index idx_test_uk on test(id)

                                        *

ERROR at line 1:

ORA-01408: such column list already indexed

即使创建非唯一索引也不行:

SQL> create index idx_test_id on test(id);

create index idx_test_id on test(id)

                                 *

ERROR at line 1:

ORA-01408: such column list already indexed

 

那么唯一键约束的情况是怎样的呢?

SQL> drop table test purge;

 

Table dropped.

 

SQL> create table test(

  id int,

  name varchar2(20),

  constraint uk_test unique(id));

 

Table created.

 

SQL> select constraint_name, constraint_type from user_constraints;

 

CONSTRAINT_NAME                C

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

UK_TEST                        U

 

查看此时的索引情况:

SQL> select index_name, index_type, uniqueness, tablespace_name

  from user_indexes

  where table_owner='SCOTT'

  and table_name = 'TEST';

 

INDEX_NAME           INDEX_TYPE           UNIQUENES TABLESPACE_NAME

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

UK_TEST              NORMAL               UNIQUE    USERS

Oracle同样自动创建了一个同名的唯一索引,而且也不允许再在此列上创建唯一索引或非唯一索引。

 

 

我们知道,主键约束要求列值非空(NOT NULL),那么唯一键约束是否也要求非空呢?

SQL> insert into test values(1, 'Sally');

 

1 row created.

 

SQL> insert into test values(null, 'Tony');

 

1 row created.

 

SQL> insert into test values(null, 'Jack');

 

1 row created.

 

SQL> select * from test;

 

        ID NAME

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

         1 Sally

           Tony

           Jack

从实验结果来看,唯一键约束并没有非空要求。

 

接下来我们看看唯一索引对列值的非空要求有什么不同。

SQL> drop table test purge;

 

Table dropped.

 

SQL> create table test(

  id int,

  name varchar2(20));

 

Table created.

 

SQL> create unique index idx_test_id on test (id);

 

Index created.

 

SQL> insert into test values(1, 'Sally');

 

1 row created.

 

SQL> insert into test values(null, 'Tony');

 

1 row created.

 

SQL> insert into test values(null, 'Jack');

 

1 row created.

 

SQL> select * from test;

 

        ID NAME

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

         1 Sally

           Tony

           Jack

通过实验,我们看出唯一索引与唯一键约束一样对列值非空不做要求。

 

如果我们让主键约束或者唯一键约束失效,Oracle自动创建的唯一索引是否会受到影响?

SQL> drop table test purge;

 

Table dropped.

 

SQL> create table test(

  id int,

  name varchar2(20),

  constraint uk_test unique(id));

 

Table created.

 

SQL> select index_name, index_type, uniqueness from user_indexes;

 

INDEX_NAME                     INDEX_TYPE                  UNIQUENES

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

UK_TEST                        NORMAL                      UNIQUE

 

SQL> alter table test disable constraint uk_test;

 

Table altered.

 

SQL> select index_name, index_type, uniqueness from user_indexes;

 

no rows selected

当主键约束或者唯一键约束失效时,Oracle会删除隐式创建的唯一索引。

 

如果我们先创建唯一索引,再创建主键或者唯一键约束,情况又会怎样呢?

SQL> drop table test purge;

 

Table dropped.

 

SQL> create table test(

  id int,

  name varchar2(20));

 

Table created.

 

SQL> create unique index idx_test_id on test (id);

 

Index created.

 

SQL> select index_name, index_type, uniqueness

  from user_indexes

  where table_owner = 'SCOTT'

  and table_name = 'TEST';

 

INDEX_NAME                     INDEX_TYPE                  UNIQUENES

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

IDX_TEST_ID                    NORMAL                      UNIQUE

 

SQL> alter table test add constraint uk_test unique (id);

 

Table altered.

 

SQL> select index_name, index_type, uniqueness

  from user_indexes

  where table_owner = 'SCOTT'

  and table_name = 'TEST';

 

INDEX_NAME                     INDEX_TYPE                  UNIQUENES

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

IDX_TEST_ID                    NORMAL                      UNIQUE

 

SQL> select constraint_name, constraint_type

  from user_constraints

  where table_name = 'TEST';

 

CONSTRAINT_NAME                C

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

UK_TEST                        U

 

SQL> alter table test disable constraint uk_test;

 

Table altered.

 

SQL> select constraint_name, constraint_type, status

  from user_constraints

  where table_name = 'TEST';

 

CONSTRAINT_NAME                C STATUS

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

UK_TEST                        U DISABLED

 

SQL> select index_name, index_type, uniqueness, status

  from user_indexes

  where table_owner = 'SCOTT'

  and table_name = 'TEST';

 

INDEX_NAME                     INDEX_TYPE                  UNIQUENES STATUS

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

IDX_TEST_ID                    NORMAL                      UNIQUE    VALID

 

实验结果表明,先创建的唯一索引不受约束失效的影响。

 

总结如下:

(1)主键约束和唯一键约束均会隐式创建同名的唯一索引,当主键约束或者唯一键约束失效时,隐式创建的唯一索引会被删除;

(2)主键约束要求列值非空,而唯一键约束和唯一索引不要求列值非空;

(3)相同字段序列不允许重复创建索引;

 

<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、付费专栏及课程。

余额充值