关于Oracle数据库表的主键，通常可以理解为非空且唯一，关于这种理解是否准确，请看下面的测试！
################################ 默认情况 #################################
lynn@10G> create table t1 (id number(8) primary key,name varchar2(10));
Table created.
lynn@10G> desc t1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NOT NULL NUMBER(8)
NAME VARCHAR2(10)
lynn@10G> insert into t1 values (1,'aa');
1 row created.
lynn@10G> insert into t1 values (2,'bb');
1 row created.
lynn@10G> select * from t1;
ID NAME
---------- ----------
1 aa
2 bb
lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T1';
INDEX_NAME INDEX_TYPE TABLE_OWNE TABLE_NAME TABLE_TYPE UNIQUENES
--------------- ---------- ---------- ---------- ----------- ---------
SYS_C005647 NORMAL LYNN T1 TABLE UNIQUE
lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T1';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ----------
LYNN SYS_C005647 P T1
lynn@10G> drop index SYS_C005647;
drop index SYS_C005647
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
lynn@10G> alter table t1 drop primary key;
Table altered.
lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T1';
no rows selected
lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T1';
no rows selected
##################### 创建表时同时创建索引并指定给主键列 #####################
lynn@10G> CREATE TABLE t2
2 (
3 id NUMBER(8) PRIMARY KEY USING INDEX
4 (CREATE INDEX t2_indx ON t2
5 (id
6 )
7 ),
8 name VARCHAR2(10)
9 );
Table created.
lynn@10G> desc t2
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NOT NULL NUMBER(8)
NAME VARCHAR2(10)
lynn@10G> insert into t2 values(1,'aa');
1 row created.
lynn@10G> insert into t2 values(2,'bb');
1 row created.
lynn@10G> insert into t2 values(2,'bb');
insert into t2 values(2,'bb')
*
ERROR at line 1:
ORA-00001: unique constraint (LYNN.SYS_C005649) violated
lynn@10G> select * from t2;
ID NAME
---------- ----------
1 aa
2 bb
lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T2';
INDEX_NAME INDEX_TYPE TABLE_OWNE TABLE_NAME TABLE_TYPE UNIQUENES
--------------- ---------- ---------- ---------- ----------- ---------
T2_INDX NORMAL LYNN T2 TABLE NONUNIQUE
lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T2';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ----------
LYNN SYS_C005649 P T2
lynn@10G> drop index T2_INDX;
drop index T2_INDX
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
lynn@10G> alter table t2 drop primary key;
Table altered.
lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T2';
INDEX_NAME INDEX_TYPE TABLE_OWNE TABLE_NAME TABLE_TYPE UNIQUENES
--------------- ---------- ---------- ---------- ----------- ---------
T2_INDX NORMAL LYNN T2 TABLE NONUNIQUE
lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T2';
no rows selected
################### 先创建表 | 再创建索引 | 再指定给主键列 ###################
lynn@10G> create table t3 (id number(8),name varchar2(10));
Table created.
lynn@10G> insert into t3 values (1,'aa');
1 row created.
lynn@10G> insert into t3 values (2,'bb');
1 row created.
lynn@10G> desc t3
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER(8)
NAME VARCHAR2(10)
lynn@10G> create bitmap index t3_indx on t3(id);
Index created.
lynn@10G> alter table t3 add primary key(id) using index t3_indx;
alter table t3 add primary key(id) using index t3_indx
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.
lynn@10G> drop index t3_indx;
Index dropped.
lynn@10G> create index t3_indx on t3(id) reverse;
Index created.
lynn@10G> alter table t3 add primary key(id) using index t3_indx;
Table altered.
lynn@10G> desc t3
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NOT NULL NUMBER(8)
NAME VARCHAR2(10)
lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T3';
INDEX_NAME INDEX_TYPE TABLE_OWNE TABLE_NAME TABLE_TYPE UNIQUENES
--------------- ---------- ---------- ---------- ----------- ---------
T3_INDX NORMAL/REV LYNN T3 TABLE NONUNIQUE
lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T3';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ----------
LYNN SYS_C005651 P T3
lynn@10G> drop index t3_indx;
drop index t3_indx
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
lynn@10G> alter table t3 drop constraint SYS_C005651;
Table altered.
lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T3';
no rows selected
lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T3';
INDEX_NAME INDEX_TYPE TABLE_OWNE TABLE_NAME TABLE_TYPE UNIQUENES
--------------- ---------- ---------- ---------- ----------- ---------
T3_INDX NORMAL/REV LYNN T3 TABLE NONUNIQUE
lynn@10G> drop index t3_indx;
Index dropped.
lynn@10G> create index t3_indx on t3 (round(id,0));
Index created.
lynn@10G> alter table t3 add primary key(id) using index t3_indx;
alter table t3 add primary key(id) using index t3_indx
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.
############################## 主键测试结论 ###############################
关于主键的官方解释链接：
http://docs.oracle.com/cd/E11882_01/server.112/e40540/datainte.htm#CNCPT322
http://docs.oracle.com/cd/E11882_01/server.112/e40540/datainte.htm#CNCPT1646
http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm#SQLRF52180
To enable a unique or primary key constraint, you must have the privileges necessary to create an index on the table. You need these privileges because Oracle Database creates an index on the columns of the unique or primary key in the schema containing the table.
primary key 可理解为如下形式：
not null constraint + unique constraint + index
1.虽然这个index可以不是unique，但却必须存在，对于Oracle DB而言，它认为这个index是unique的，这个索引不能是函数和位图索引；
2.如果主键列上的索引是人为指定创建的，当主键约束被删除后，主键列上的索引不会像默认情况那样也被删除；
3.通常理解的primary key是not null + unique index也没错，因为not null + unique index可以实现主键的作用，但是主键的唯一性不是通过主键列上的这个index来保证实现，而是通过主键约束本身来保证唯一性的！