数据库索引键uk_主键、唯一键约束、索引区别与联系

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

键是一个逻辑概念

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

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

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