【INDEX】Oracle中主键、唯一约束与唯一索引之区别

【INDEX】Oracle中主键、唯一约束与唯一索引之区别

一、 概述

一般在 Oracle 数据表中，我们都会看到主键，索引，也会看到唯一索引、唯一约束，那么他们有什么区别呢，下面通过一个小实验简单了解一下。

二、 实验过程：
首先创建一个表，并完成主键、唯一约束、唯一索引的创建，如下所示：

SQL>-- 数据库版本
SQL> select * from v$version;

BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 – Production

SQL> conn firsoul/firsoul

Connected.

SQL> – 创建测试表

SQL> create table test (tno number(5),tname varchar2(20),tid int);

Table created.

SQL>-- 添加主键

SQL> alter table test add constraint pk_test_tno primary key(tno);

Table altered.

SQL>-- 添加唯一约束

SQL> alter table test add constraint uk_test_tname unique(tname);

Table altered.

SQL>-- 添加唯一索引

SQL> create unique index idx_test_tid on test(tid);

Index created.

向表 TEST 中插入数据，测试

SQL> insert into test values(1,‘firsoul’,123);

1 row created.

SQL> 1

1* insert into test values(1,‘firsoul’,123)

SQL> /

insert into test values(1,‘firsoul’,123)

ERROR at line 1:

ORA-00001: unique constraint (FIRSOUL.PK_TEST_TNO) violated

SQL> insert into test values(2,‘firsoul’,123456);

insert into test values(2,‘firsoul’,123456)

ERROR at line 1:

ORA-00001: unique constraint (FIRSOUL.UK_TEST_TNAME) violated

SQL> insert into test values(2,‘ljx’,123);

insert into test values(2,‘ljx’,123)

ERROR at line 1:

ORA-00001: unique constraint (FIRSOUL.IDX_TEST_TID) violated
SQL> insert into test values(null,‘ljx’,124);

insert into test values(null,‘ljx’,124)

                    *

ERROR at line 1:

ORA-01400: cannot insert NULL into (“FIRSOUL”.“TEST”.“TNO”)

SQL> insert into test values(2,null,null);

1 row created.

SQL> commit;

Commit complete.

有以上信息，我们可以看出主键列的值不允许为 NULL ，也不允许出现重复值，唯一约束、唯一索引列的值允许为 NULL ，但不允许出现重复值。
查看字典表中约束的情况

SQL> set lines 150

SQL> col table_name for a15

SQL> col column_name for a15

SQL> col constraint_name for a20

SQL> select table_name,column_name,constraint_name from user_cons_columns where table_name=upper(‘test’);

TABLE_NAME COLUMN_NAME CONSTRAINT_NAME

---

TEST TNO PK_TEST_TNO

TEST TNAME UK_TEST_TNAME

查看字典表中索引的情况

SQL> select table_name,column_name,index_name from user_ind_columns where table_name=upper(‘test’);

TABLE_NAME COLUMN_NAME INDEX_NAME

---

TEST TNO PK_TEST_TNO

TEST TNAME UK_TEST_TNAME

TEST TID IDX_TEST_TID

SQL> select index_name,uniqueness,tablespace_name from user_indexes where table_name=upper(‘test’);

INDEX_NAME UNIQUENES TABLESPACE_NAME

---

IDX_TEST_TID UNIQUE FIRSOUL

UK_TEST_TNAME UNIQUE FIRSOUL

PK_TEST_TNO UNIQUE FIRSOUL

在唯一索引上创建唯一约束， TID 列增加了约束，并没有继续增加唯一索引。

SQL> alter table test add constraint uk_test_tid unique(tid);

Table altered.

SQL> set lines 150

SQL> col table_name for a15

SQL> col column_name for a15

SQL> col constraint_name for a20

SQL> select table_name,column_name,constraint_name from user_cons_columns where table_name=upper(‘test’);

TABLE_NAME COLUMN_NAME CONSTRAINT_NAME

---

TEST TNO PK_TEST_TNO

TEST TNAME UK_TEST_TNAME

TEST TID UK_TEST_TID

SQL> select table_name,column_name,index_name from user_ind_columns where table_name=upper(‘test’);

TABLE_NAME COLUMN_NAME INDEX_NAME

---

TEST TNO PK_TEST_TNO

TEST TNAME UK_TEST_TNAME

TEST TID IDX_TEST_TID

通过上边我们可以看出，创建主键、唯一约束时，会同时创建与约束一致名字的索引。
下面我们删除唯一约束，查看效果

SQL> alter table test drop constraint UK_TEST_TNAME;

Table altered.

SQL> alter table test drop constraint UK_TEST_TID;

Table altered.

SQL> select table_name,column_name,constraint_name from user_cons_columns where table_name=upper(‘test’);

TABLE_NAME COLUMN_NAME CONSTRAINT_NAME

---

TEST TNO PK_TEST_TNO

SQL> select table_name,column_name,index_name from user_ind_columns where table_name=upper(‘test’);

TABLE_NAME COLUMN_NAME INDEX_NAME

---

TEST TNO PK_TEST_TNO

TEST TID IDX_TEST_TID

通过上面结果我们可以得知，手动创建索引后再创建约束，删除约束，不会删除索引，创建约束自动创建的索引会在约束失效或删除后自动删除。

三、 总结
通过以上实验我们可以知道

1 、主键约束要求列值非空，而唯一键约束和唯一索引不要求列值非空，都不能有重复数据。

2 、主键约束和唯一键约束会隐式创建同名的唯一索引，当主键约束或者唯一键约束失效时，隐式创建的唯一索引会被删除。

3 、相同字段序列不允许重复创建索引。