当前位置:Gxlcms > mysql > 关于创建主键和索引的关系一个小小测试

关于创建主键和索引的关系一个小小测试

时间:2021-07-01 10:21:17 帮助过:22人阅读

创建主键时,所对应的列如果没有索引,数据库默认会自动创建一个索引;如果对于列有索引,那么创建主键不会再创建索引。这里要注意,列值必须满足主键的要求(唯一,非空),简单测试如下: SQL create table wxlun_pri(a number); Table created. SQL alter t

创建主键时,所对应的列如果没有索引,数据库默认会自动创建一个索引;如果对于列有索引,那么创建主键不会再创建索引。这里要注意,列值必须满足主键的要求(唯一,非空),简单测试如下:

SQL> create table wxlun_pri(a number);


Table created.

SQL> alter table wxlun_pri add constraint PRIMARY_KEY_REPLY primary key (a); ------创建主键约束

Table altered.

SQL> select index_name from user_indexes where table_name='WXLUN_PRI'; ------默认生成索引

INDEX_NAME
------------------------------
PRIMARY_KEY_REPLY

SQL> alter table wxlun_pri drop constraint PRIMARY_KEY_REPLY;

Table altered.

SQL> select index_name from user_indexes where table_name='WXLUN_PRI';

no rows selected

SQL> create index idx_wxlun_pri on wxlun_pri(a); ------创建一个一般索引,测试有重复值时添加主键约束

Index created.

SQL> insert into wxlun_pri values(1);

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from wxlun_pri;

A
----------
1
1

SQL> alter table wxlun_pri add constraint PRIMARY_KEY_REPLY primary key (a); ------原索引有重复值,添加主键约束失败
alter table wxlun_pri add constraint PRIMARY_KEY_REPLY primary key (a)
*
ERROR at line 1:
ORA-02437: cannot validate (WXLUN.PRIMARY_KEY_REPLY) - primary key violated

删除重复值,添加主键约束

SQL> delete from wxlun_pri where rownum<2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from wxlun_pri;

A
----------
1

SQL> alter table wxlun_pri add constraint PRIMARY_KEY_REPLY primary key (a); ------成功

Table altered.

SQL> select index_name from user_indexes where table_name='WXLUN_PRI';

INDEX_NAME
------------------------------
IDX_WXLUN_PRI

SQL> drop table wxlun_pri;

Table dropped.

SQL>

人气教程排行