主键与索引（一）

最新推荐文章于 2024-01-11 16:20:09 发布

ckawt40802

最新推荐文章于 2024-01-11 16:20:09 发布

阅读量178

点赞数

文章标签：数据库

刚刚看完文档中这部分的描述，简单总结一下。如无特别说明，这里的主键泛指主键和唯一约束。

这一篇介绍主键的创建与索引的关系。

一般而言，在创建主键会自动创建一个唯一索引，这个索引可以在主键创建的时候指定名称和存储参数。

主键除了可以使用唯一索引，也可以使用非唯一索引。

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已创建。

SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, INDEX_NAME
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'T';

CONSTRAINT_NAME C INDEX_NAME
------------------------------ - ------------------------------
SYS_C007408 P SYS_C007408

SQL> SELECT INDEX_NAME, UNIQUENESS, TABLESPACE_NAME
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'T';

INDEX_NAME UNIQUENES TABLESPACE_NAME
------------------------------ --------- -------------------------------------------
SYS_C007408 UNIQUE USERS

SQL> ALTER TABLE T DROP PRIMARY KEY;

表已更改。

SQL> ALTER TABLE T ADD PRIMARY KEY (ID) USING INDEX (CREATE INDEX IND_T_ID ON T(ID) TABLESPACE TEST);

表已更改。

SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, INDEX_NAME
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'T';

CONSTRAINT_NAME C INDEX_NAME
------------------------------ - ------------------------------
SYS_C007409 P IND_T_ID

SQL> SELECT INDEX_NAME, UNIQUENESS, TABLESPACE_NAME
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'T';

INDEX_NAME UNIQUENES TABLESPACE_NAME
------------------------------ --------- --------------------------------------------
IND_T_ID NONUNIQUE TEST

除了创建主键时自动创建索引外，也可以使用已有的索引：

SQL> DROP TABLE T PURGE;

表已删除。

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> CREATE INDEX IND_T_ID_NAME ON T(ID, NAME);

索引已创建。

SQL> ALTER TABLE T ADD PRIMARY KEY (ID);

表已更改。

SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, INDEX_NAME
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'T';

CONSTRAINT_NAME C INDEX_NAME
------------------------------ - ------------------------------
SYS_C007410 P IND_T_ID_NAME

SQL> ALTER TABLE T ADD UNIQUE (NAME) USING INDEX IND_T_ID_NAME;
ALTER TABLE T ADD UNIQUE (NAME) USING INDEX IND_T_ID_NAME
*
第 1 行出现错误:
ORA-14196: 指定的索引不能用于强制约束条件。

如果当前已经存在的用户创建的索引可以满足主键的条件，那么默认情况下，Oracle不会再去创建一个新的主键，既是这个索引的列并不完全和主键列一致。

事实上，只要约束的列是索引的前缀列，Oracle就可以使用这个索引，而与前缀列中的顺序没有关系，但是非前缀列是无法利用这个索引的，上面的报错就说明了这一点。

SQL> ALTER TABLE T ADD UNIQUE (NAME, ID);

表已更改。

SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, INDEX_NAME
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'T';

CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
SYS_C007410                    P IND_T_ID_NAME
SYS_C007412                    U IND_T_ID_NAME

SQL> ALTER TABLE T ADD UNIQUE (ID, NAME);

表已更改。

SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, INDEX_NAME
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'T';

CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
SYS_C007410                    P IND_T_ID_NAME
SYS_C007412                    U IND_T_ID_NAME
SYS_C007413                    U IND_T_ID_NAME

SQL> SELECT INDEX_NAME, UNIQUENESS, TABLESPACE_NAME
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'T';

INDEX_NAME UNIQUENES TABLESPACE_NAME
------------------------------ --------- ------------------
IND_T_ID_NAME NONUNIQUE USERS

可以看到，索引列的顺序与约束的顺序不同也可以利用这个索引。而且三个不同的约束使用了同一个索引。

Oracle默认会使用已有索引作为主键的约束索引，并不会倾向于创建新的索引，这个观点成立吗？而Oracle文档上提到的一个主键或唯一约束最多使用一个非唯一索引又是什么含义呢？Oracle文档上原文如下：

At most one unique or primary key can use each nonunique index.

而上面的例子事实上已经推翻了这个观点。

其实对于用户创建的索引和Oracle为了主键约束自动创建的索引还是有区别的：

SQL> DROP TABLE T PURGE;

表已删除。

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30), PRIMARY KEY (ID, NAME));

表已创建。

SQL> ALTER TABLE T ADD UNIQUE (ID);

表已更改。

SQL> ALTER TABLE T ADD UNIQUE (NAME, ID);

表已更改。

SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, INDEX_NAME
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'T';

CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
SYS_C007416                    P SYS_C007416
SYS_C007417                    U SYS_C007417
SYS_C007418                    U SYS_C007416

可以看到，对于ID上的唯一约束，Oracle去创建了一个新的索引，而对于NAME和ID上的唯一约束，Oracle使用了ID和NAME上已有的索引。