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

Oracle主键、唯一键与唯一索引的区别
 
一般,我们看到术语“索引”和“键”交换使用,但实际上这两个是不同的。索引是存储在 数据库中的一个物理结构,键纯粹是一个逻辑概念。键代表创建来实施业务规则的完整性约束。索引和键的混淆通常是由于数据库使用索引来实施完整性约束。
 
接下来我们看看数据库中的主键约束、唯一键约束和唯一索引的区别。
伦理片   http://www.dotdy.com/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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 ,
   name  varchar2(20),
   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数据库会自动创建一个同名的唯一索引:
?
1
2
3
4
5
6
7
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会报错,因为该列上已经存在一个唯一索引:
?
1
2
3
4
5
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

 

 
即使创建非唯一索引也不行:
?
1
2
3
4
5
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

 

 
那么唯一键约束的情况是怎样的呢?
?
1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> drop  table  test purge;
Table  dropped.
  
SQL> create  table  test(
   2  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

 

 
查看此时的索引情况:
?
1
2
3
4
5
6
7
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),那么唯一键约束是否也要求非空呢?
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

 

 
从实验结果来看,唯一键约束并没有非空要求。
 
接下来我们看看唯一索引对列值的非空要求有什么不同。
 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SQL> drop  table  test purge;
Table  dropped.
  
SQL> create  table  test(
   2  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自动创建的唯一索引是否会受到影响?
 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SQL> drop  table  test purge;
Table  dropped.
SQL> create  table  test(
   2  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
  
SQL> alter  table  test disable constraint  uk_test;
Table  altered.
  
SQL> select  index_name, index_type, uniqueness from  user_indexes;
INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
UK_TEST                        NORMAL                      UNIQUE

 

 
当主键约束或者唯一键约束失效时,Oracle会标记隐式创建的唯一索引为删除状态。
=====================================================
Using Nonunique Indexes to Enforce Uniqueness
You can use an existing nonunique index on a table to enforce uniqueness, either for UNIQUE constraints or the unique  aspect of a PRIMARY KEY constraint. The advantage of this approach is that the index remains available and valid when the constraint is disabled. Therefore, enabling a disabled UNIQUE or PRIMARY KEY constraint does not require rebuilding the unique index associated with the constraint. This can yield significant time savings on enable operations for large tables.
Using a nonunique index to enforce uniqueness also lets you eliminate redundant indexes. You do not need a unique index on a primary key column if that column is included as the prefix of a composite index. You can use the existing index to enable and enforce the constraint. You also save significant space by not duplicating the index. However, if the existing index is partitioned, then the partitioning key of the index must also be a subset of the UNIQUE key; otherwise,  Oracle Database creates an additional unique index to enforce the constraint.
=====================================================
如果我们先创建唯一索引,再创建主键或者唯一键约束,情况又会怎样呢?
 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
SQL> drop  table  test purge;
Table  dropped.
  
SQL> create  table  test(
   2  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
  
实验结果表明,先创建的唯一索引不受约束失效的影响。
  
SCOTT@ orcl> create  index  idx_test_id on  test(id);
Index  created.
  
SCOTT@ orcl> alter  table  test add  constraint  uk_test unique  (id);
Table  altered.
  
SCOTT@ orcl> insert  into  test values (1, 'liu' );
insert  into  test values (1, 'liu' )
*
ERROR at  line 1:
ORA-00001: unique  constraint  (SCOTT.UK_TEST) violated
  
SCOTT@ orcl> alter  table  test drop  constraint  uk_test;
Table  altered.
  
SCOTT@ orcl> insert  into  test values (1, 'liu' );
1 row created.
  
SCOTT@ orcl> delete  from  test where  id=1 and  rownum=1;
1 row deleted.
  
SCOTT@ orcl> select  * from  test;
         ID NAME
---------- --------------------
            yang
          1 liu
  
SCOTT@ orcl> create  unique  index  idx_test_id on  test(id);
Index  created.
  
SCOTT@ orcl> alter  table  test add  constraint  uk_test unique  (id);
Table  altered.
  
SCOTT@ orcl> alter  table  test drop  constraint  uk_test;
Table  altered.
  
SCOTT@ orcl> insert  into  test values (1, 'liu' );
insert  into  test values (1, 'liu' )
*
ERROR at  line 1:
ORA-00001: unique  constraint  (SCOTT.IDX_TEST_ID) violated

 

 
总结如下:
(1)主键约束和唯一键约束均会隐式创建同名的唯一索引,当主键约束或者唯一键约束失效时,隐式创建的唯一索引会被删除;
(2)主键约束要求列值非空,而唯一键约束和唯一索引不要求列值非空;
(3)相同字段序列不允许重复创建索引;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值