一。利用已有索引创建主键
1.建表
GAO@PROD> create table abcd(id number(10),name1 varchar2(20));
Table created.
2.插入数据
GAO@PROD> insert into abcd values(1,'genwang');
1 row created.
GAO@PROD> commit;
Commit complete.
3.给id字段创建索引
GAO@PROD> create index pk_abcd on abcd(id);
Index created.
4.利用已有索引创建主键
GAO@PROD> alter table abcd add constraint pk_abcd primary key(id);
Table altered.
5.查看表的索引和约束
GAO@PROD> select index_name from user_indexes where table_name=upper('abcd');
INDEX_NAME
------------------------------------------------------------------------------------------
PK_ABCD
GAO@PROD> select constraint_name from user_constraints where table_name=upper('abcd');
CONSTRAINT_NAME
------------------------------------------------------------------------------------------
PK_ABCD
6.删除主键
GAO@PROD> alter table abcd drop primary key;
Table altered.
7.查看索引和约束是否已经被删除，发现索引还在，约束没了
GAO@PROD> select index_name from user_indexes where table_name=upper('abcd');
INDEX_NAME
------------------------------------------------------------------------------------------
PK_ABCD
GAO@PROD> select constraint_name from user_constraints where table_name=upper('abcd');
no rows selected
8.用别的字段重建主键，发现报错 ORA-00955
alter table abcd add constraint pk_abcd primary key(id,name1);
GAO@PROD> alter table abcd add constraint pk_abcd primary key(id,name1);
alter table abcd add constraint pk_abcd primary key(id,name1)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
解决办法：
1)删除原来的索引，然后再创建新的主键
GAO@PROD> drop index pk_abcd;
Index dropped.
2)GAO@PROD> alter table abcd add constraint pk_abcd primary key(id,name1);
Table altered.
9.查看此时abcd表的索引和约束（可以看到如果该表主键列没有索引，这时创建主键时会自动在主键列创建索引）
GAO@PROD> select index_name,uniqueness from user_indexes where table_name=upper('abcd');
INDEX_NAME UNIQUENESS
------------------------------------------------------------------------------------------ ---------------------------
PK_ABCD UNIQUE
GAO@PROD> select constraint_name from user_constraints where table_name=upper('abcd');
CONSTRAINT_NAME
------------------------------------------------------------------------------------------
PK_ABCD
9.删除主键
GAO@PROD> alter table abcd drop primary key;
Table altered.
10.查看索引和约束状态(发现索引和约束都不复存在)
GAO@PROD> select index_name from user_indexes where table_name=upper('abcd');
no rows selected
GAO@PROD> select constraint_name from user_constraints where table_name=upper('abcd');
no rows selected
11.此时添加新的主键,不再报错
alter table abcd add constraint pk_abcd primary key(id,name1);
结论：如果主键是基于原来索引创建的，那么alter table XXX drop primary key只能删除约束，无法删除原来索引。如果此时创建主键时，主键列与索引列不同，那么就会报错ORA-00955: name is already used by an existing object。
如果主键创建时，主键列没有索引，那么创建主键时会自动在主键列创建唯一索引，这时alter table XXX drop primary key就会把约束和索引一起删除。重新创建主键时就不会报错。