主键、唯一键约束、唯一索引

键是一个逻辑概念

索引是存储在数据库中的一个物理结构

 

1.一般情况下，在为列指定主键之后，Oracle会自动为列设置唯一性约束

当约束列上没有索引时，在创建unique constraint 时，oracle 会自动创建unique index，并且该索引不能删除，当删除unique constraint 时，unique index 会自动删除。

Oracle 自动创建了索引并关联到约束, 索引名和约束名是相同的。

 

如果列上已经存在索引，就重用之前的索引。

2.主键约束要求列值非空，而唯一键约束和唯一索引不要求列值非空；

3.形同字段序列不允许重复创建索引；

4.一个表只能有一个主键primary key，但可以有多个unique key；

5.主键和唯一键约束不能建在同一列上。

 

示例1：主键与唯一索引的关系

1）创建表

SQL> create table test

  2  (

  3  id number(5),

  4  name varchar2(15),

  5  constraint pk_test primary key(id)

  6  );

表已创建。

 

查看主键

SQL> select constraint_name,constraint_type from  user_constraints where constraint_name like '%TEST%';

 

CONSTRAINT_NAME                C

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

PK_TEST                        P

 

查看唯一索引--指定了ID列作为主键，oracle数据库会自动创建一个同名的唯一索引

SQL> select index_name,index_type,uniqueness,tablespace_name

  2  from user_indexes

  3  where table_name='TEST';

INDEX_NAME      INDEX_TYPE        UNIQUENES     TABLESPACE_NAME

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

PK_TEST         NORMAL            UNIQUE        SYSTEM

 

2）试图在ID列上创建一个唯一索引或非唯一索引，Oracle会报错，因为该列上已经存在一个唯一索引

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

create unique index idx_test_uk on test(id)

                                        *

第 1 行出现错误:

ORA-01408: 此列列表已索引

 

SQL> create index idx_test_id on test(id);

create index idx_test_id on test(id)

                                 *

第 1 行出现错误:

ORA-01408: 此列列表已索引

 

3）未删除主键时，试图删除索引，oracle会返回错误

SQL> drop index PK_TEST;

drop index PK_TEST

           *

第 1 行出现错误:

ORA-02429: 无法删除用于强制唯一/主键的索引

 

4）主键约束失效时，唯一索引会被删除

SQL> select constraint_name,constraint_type from  user_constraints where constraint_name like '%TEST%';

CONSTRAINT_NAME                C

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

PK_TEST                        P

 

SQL> select index_name,index_type,uniqueness,tablespace_name

  2  from user_indexes

  3  where table_name='TEST';

未选定行

 

5）使主键重新启用，唯一索引被重新建立

SQL> alter table test enable constraint pk_test;

表已更改。

 

SQL> select index_name,index_type,uniqueness,tablespace_name

  2  from user_indexes

  3  where table_name='TEST';

INDEX_NAME         INDEX_TYPE      UNIQUENES  TABLESPACE_NAME

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

PK_TEST            NORMAL          UNIQUE     SYSTEM

 

 

示例2：唯一键约束和唯一索引

SQL> create table test

  2  (

  3  id number(5),

  4  name varchar2(15),

  5  constraint uk_test unique(id)

  6  );

表已创建。

 

查看唯一键约束

SQL> select constraint_name,constraint_type from  user_constraints where constraint_name like '%TEST%';

CONSTRAINT_NAME                C

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

UK_TEST                        U

 

查看唯一索引

SQL> select index_name,index_type,uniqueness,tablespace_name

  2  from user_indexes

  3  where table_name='TEST';

INDEX_NAME       INDEX_TYPE      UNIQUENES   TABLESPACE_NAME

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

UK_TEST           NORMAL         UNIQUE        SYSTEM

 

2）试图在ID列上创建一个唯一索引或非唯一索引，Oracle会报错，因为该列上已经存在一个唯一索引

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

create unique index idx_test_uk on test(id)

                                        *

第 1 行出现错误:

ORA-01408: 此列列表已索引

 

 

SQL>  create index idx_test_id on test(id);

 create index idx_test_id on test(id)

                                  *

第 1 行出现错误:

ORA-01408: 此列列表已索引

 

3）---5）同主键一样，使唯一键约束失效后，唯一索引也被删除。

 

示例3,：查看主键和唯一键约束 唯一视图对空值的要求

主键约束要求列值非空，而唯一键约束和唯一索引不要求列值非空；

3.1 主键不允许插入空值

SQL> create table t (a int,b int,c int,d int);

Table created.

SQL> desc t

 Name                                      Null?    Type

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

 A                                                  NUMBER(38)

 B                                                  NUMBER(38)

 C                                                  NUMBER(38)

 D                                                  NUMBER(38)

SQL> alter table t add constraint pk_t primary key (a,b);

Table altered.

SQL> desc t

 Name                                      Null?    Type

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

 A                                         NOT NULL NUMBER(38)

 B                                         NOT NULL NUMBER(38)

 C                                                  NUMBER(38)

 D                                                  NUMBER(38)

可以看到A、B两个列都自动改为了NOT NULL

SQL> alter table t modify (a int null);

alter table t modify (a int null)

                      *

ERROR at line 1:

ORA-01451: column to be modified to NULL cannot be modified to NULL

可以看到，主键列A不允许改为NULL

 

SQL> alter table t drop constraint pk_t;

Table altered.

SQL> alter table t add constraint uk_t_1 unique (a,b);

Table altered.

SQL> desc t

 Name                                      Null?    Type

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

 A                                                  NUMBER(38)

 B                                                  NUMBER(38)

 C                                                  NUMBER(38)

 D                                                  NUMBER(38)

我们看到列A又变回了NULL。

注意到，在删除主键时，列的NULLABLE会回到原来的状态。如果在创建主键后，对原来为NULL的主键列，显式设为NOT NULL，在删除主键后仍然是NOT NULL。比如在创建主键后，执行下面的操作，

 

3.2 唯一键约束允许插入空值

SQL> alter table t drop constraint pk_t;

Table altered.

SQL> insert into t (a ,b ) values (null,null);

1 row created.

SQL> /

1 row created.

SQL> insert into t (a ,b ) values (null,1);

1 row created.

SQL> /

insert into t (a ,b ) values (null,1)

*

ERROR at line 1:

ORA-00001: unique constraint (SYS.UK_T_1) violated

 

SQL> insert into t (a ,b ) values (1,null);

1 row created.

SQL> /

insert into t (a ,b ) values (1,null)

*

ERROR at line 1:

ORA-00001: unique constraint (SYS.UK_T_1) violated

主键和唯一键约束是通过参考索引实施的，如果插入的值均为NULL，则根据索引的原理，全NULL值不被记录在索引上，所以插入全NULL值时，可以有重复的，而其他的则不能插入重复值。

 

3.3 唯一索引允许插入

SQL> create table test

  2  (

  3  id number(5),

  4  name varchar2(15)

  5  );

表已创建。

 

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

索引已创建。

 

SQL> select constraint_name,constraint_type from  user_constraints where constraint_name like '%TEST%';

未选定行

 

SQL> select index_name,index_type,uniqueness,tablespace_name

  2  from user_indexes

  3  where table_name='TEST';

INDEX_NAME                     INDEX_TYPE                  UNIQUENES

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

TABLESPACE_NAME

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

IDX_TEST_ID                    NORMAL                      UNIQUE

SYSTEM

 

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

已创建 1 行。

 

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

已创建 1 行。

 

示例4.创建索引之后再创建唯一性约束，唯一性约束的删除与否不会影响索引的存在

接示例3的表

创建唯一键约束

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

表已更改。

 

SQL> select constraint_name,constraint_type from  user_constraints where constraint_name like '%TEST%';

CONSTRAINT_NAME                C

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

UK_TEST                        U

 

使唯一键约束失效

SQL> select constraint_name,constraint_type,status from  user_constraints where constraint_name like '%TEST%';

CONSTRAINT_NAME                C STATUS

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

UK_TEST                        U DISABLED

 

索引依然存在

SQL> select index_name,index_type,uniqueness,tablespace_name

  2  from user_indexes

  3  where table_name='TEST';

INDEX_NAME          INDEX_TYPE      UNIQUENES    TABLESPACE_NAME

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

IDX_TEST_ID         NORMAL          UNIQUE       SYSTEM

 

示例5.一个表只能增加一个主键，可以增加多个唯一键

SQL> drop table t;

Table dropped.

SQL> create table t (a int,b int,c int,d int);

Table created.

SQL> alter table t add constraint uk_t_1 unique (a,b);

Table altered.

SQL> alter table t add constraint uk_t_2 unique (c,d);

Table altered.

可以看到可以增加两个UNIQUE KEY。看看能不能增加两个主键：

SQL> alter table t add constraint pk_t primary key (c);

Table altered.

SQL> alter table t add constraint pk1_t primary key (d);

alter table t add constraint pk1_t primary key (d)

                                  *

ERROR at line 1:

ORA-02260: table can have only one primary key

由此可以看到一个表只能有一个主键。