mysql和oracle唯一索引的区别_Oracle 主键、唯一键与唯一索引的区别

一般，我们看到术语ldquo;索引rdquo;和ldquo;键rdquo;交换使用，但实际上这两个是不同的。索引是存储在数据库中的一个物理结

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

推荐阅读：

Oracle主键约束自动建立索引问题

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

介绍Oracle Virtual Index虚拟索引

Oracle中检查是否需要重构索引

Oracle case when索引 空值索引 位图索引

Oracle分析表和索引

Oracle主键约束自动建立索引问题

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

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 (

2 id int,

3 name varchar2(20),

4 constraint pk_test primary key(id))

5 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

2 from user_indexes

3 where table_owner='SCOTT'

4 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(

2 id int,

3 name varchar2(20),

4 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

2 from user_indexes

3 where table_owner='SCOTT'

4 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(

2 id int,

3 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

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

继续阅读本文的精彩内容请看第2页：

，

本文原创发布php中文网，转载请注明出处，感谢您的尊重！