`

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

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

 

 

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

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

2 id int,

3 name varchar2(20));

 

Table created.

 

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

 

Index created.

 

SQL> select index_name, index_type, uniqueness

2 from user_indexes

3 where table_owner = 'SCOTT'

4 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

2 from user_indexes

3 where table_owner = 'SCOTT'

4 and table_name = 'TEST';

 

INDEX_NAME INDEX_TYPE UNIQUENES

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

IDX_TEST_ID NORMAL UNIQUE

 

SQL> select constraint_name, constraint_type

2 from user_constraints

3 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

2 from user_constraints

3 where table_name = 'TEST';

 

CONSTRAINT_NAME C STATUS

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

UK_TEST U DISABLED

 

SQL> select index_name, index_type, uniqueness, status

2 from user_indexes

3 where table_owner = 'SCOTT'

4 and table_name = 'TEST';

 

INDEX_NAME INDEX_TYPE UNIQUENES STATUS

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

IDX_TEST_ID NORMAL UNIQUE VALID

 

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

 

总结如下:

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

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

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

分享到:
评论

相关推荐

    oracle约束和索引笔记

    主键约束同时包含了非空和唯一约束,不允许有重复或NULL值。 - **外键约束(FOREIGN KEY)**: 外键约束用于建立表之间的关系,确保引用完整性。一个表的外键字段是另一个表的主键字段的值。 - **检查约束(CHECK)...

    oracle创建表创建唯一索引

    以下将详细介绍如何创建学员信息表,创建唯一索引,以及如何修改表来添加主键和检查约束。 首先,我们来理解"创建学员信息表"。在Oracle中,我们可以使用`CREATE TABLE`语句来创建新的表。一个典型的学员信息表可能...

    goldengate同步无主键无唯一索引表的问题以及解决方案.docx

    GoldenGate 同步无主键无唯一索引表的问题及解决方案 在数据同步中,无主键无唯一索引表的同步问题是常见的困扰, GoldenGate 作为一款优秀的数据同步工具,也遇到了这个问题。因此,本文将讨论 GoldenGate 同步无...

    数据库,索引,主键,约束,sql

    这些规则可以是实体完整性(如主键约束),确保数据不为空;参照完整性(如外键约束),保证引用的外键值存在于被引用表的主键中;或者用户自定义完整性,如检查约束,确保数据符合特定条件。约束帮助防止错误数据的...

    如何在oracle中查询所有用户表的表名、主键名称、索引、外键等

    通过设置 `constraint_type = 'P'` 来筛选出主键约束。`constraint_name` 在两个表中被用作关联字段,从而能够获取到准确的主键信息。 ### 3. 查询所有用户表的唯一性约束 #### SQL 语句: ```sql SELECT column_...

    Oracle字段约束.docx

    主键约束不仅确保了数据的唯一性,同时也自动为该字段添加了索引,从而加速查询速度。 其次,唯一性约束(UNIQUE)与主键约束类似,但不强制非空。这意味着某个字段的值在表中必须是唯一的,但允许有NULL值。例如,...

    Oracle 索引

    这对于主键和唯一约束非常有用,可以防止数据冗余和不一致性。 5. **复合索引**:复合索引由多个列组成,根据所有列的组合来排序和存储记录。选择合适的复合索引列顺序对于查询性能至关重要。 索引的创建、维护和...

    Oracle中两个主要的用户、三个重要权限的区别和联系

    ### Oracle中两个主要的用户、三个重要权限的区别和联系 #### 一、Oracle中的两个主要用户:SYS与SYSTEM 在Oracle数据库系统中,SYS与SYSTEM是两个非常重要的内置用户,它们各自拥有不同的权限和功能。 - **SYS...

    oracle删除主键查看主键约束及创建联合主键

    本篇文章将详细讲解如何在Oracle中删除主键、查看主键约束,以及创建联合主键。 1. 删除主键 在Oracle中,删除主键约束有几种方法。首先,你可以尝试使用`ALTER TABLE`语句删除主键,语法如下: ```sql ALTER ...

    ORACLE的索引和约束详解.pdf

    外键约束保证了数据的一致性和参照完整性,它定义了一个字段或一组字段与另一个表的主键或唯一键之间的关系。外键字段只能包含被引用表的引用码或NULL值。外键约束可以是自引用的,即在一个表内引用自身的字段。例如...

    oracle中添加删除主键的方法

    本文将详细介绍在Oracle中如何添加和删除主键约束,以及一些相关的操作方法。 首先,我们来看如何在创建表时添加主键约束: 1. **无命名创建主键**: 当创建表时,可以直接在字段定义中指定`primary key`作为约束...

    MySQL索引之主键索引

    在MyISAM引擎中,唯一索引和主键索引的区别不大,除非唯一索引包含NULL值。而在InnoDB中,主键作为聚集索引,而其他唯一索引和辅助索引仅作为非聚集索引。这导致了查询性能上的差异,辅助索引查询可能需要额外的步骤...

    oracle表主键和外键的区别

    **唯一键**与主键相似,也是保证数据唯一性的约束,但有以下不同: 1. 允许空值:唯一键允许包含空值,而主键不允许。 2. 不是主键:一个表可以有多个唯一键,但只有一个主键。 **左联接**和**右联接**是SQL中的两...

    oracle删除表空间出现”无法删除用于强制唯一主键的索引“解决方法[参照].pdf

    Oracle 删除表空间出现“无法删除用于强制唯一主键的索引”解决方法 在 Oracle 数据库中,删除表空间时可能会出现“无法删除用于强制唯一主键的索引”的错误。这种情况下,无法删除表空间,需要找到解决方法。在...

    SQL Server和Oracle中索引介绍

    这在需要确保数据唯一性的场景中很有用,如主键约束。 包含性列索引是特殊的非聚集索引,不仅包含键列,还包含其他列。这使得查询可以直接从索引中获取所需的所有信息,无需访问数据页,提高了查询速度。这种索引...

    oracle索引.pdf

    这种索引适用于一般索引和唯一约束索引。位图索引则适合于存储重复值多的字段,通过位图偏移来与行ID对应,但在OLTP环境中使用较少,因为频繁的DML操作可能导致锁等待和死锁。而在OLAP环境中,位图索引能有效地节省...

    oracle约束详解

    使用`USING INDEX`关键字可以为唯一性约束指定索引存储空间: ```sql ALTER TABLE Employees ADD CONSTRAINT emp_uk UNIQUE (EName, Phone) USING INDEX TABLESPACE indx; ``` #### 外键约束(Foreign Key) 外键...

    ORACLE教材

    主键约束 外部约束 唯一性约束 CHECK约束 NOT NULL约束 利用Enterprise Manager管理约束 第八章:管理索引、视图与序列使用约束 索引概述 B树索引(默认) 位图索引 函数索引 视图 序列 利用OEM操作 ...

Global site tag (gtag.js) - Google Analytics