Oracle创建主键的三种方式

SQL> create table t_pk_01 (id number, constraint pk_id_01 primary key(id));
Table created.
复制

SQL> select table_name, constraint_name, constraint_type from user_constraints where table_name='T_PK_01';
TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
T_PK_01                        PK_ID_01                       P

SQL> select table_name, index_name, uniqueness from user_indexes where table_name='T_PK_01';
TABLE_NAME                     INDEX_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
T_PK_01                        PK_ID_01                       UNIQUE
复制

SQL> create table t_pk_02 (id number);
Table created.

SQL> alter table t_pk_02 add constraint pk_id_02 primary key (id);
Table altered.
复制

SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.

SQL> alter session set tracefile_identifier='bisal';
Session altered.

SQL> alter table t_pk_02 add constraint pk_id_02 primary key (id);
Table altered.

SQL> alter session set events '10046 trace name context off';
Session altered.

SQL> select distinct(m.sid),p.pid,p.tracefile from v$mystat m,v$session s,v$process p where m.sid=s.sid and s.paddr=p.addr;
       SID        PID   TRACEFILE
---------- ----------- -------------------------------------------------------------------------------
       189         22   u01/app/oracle/diag/rdbms/dcm/DCM/trace/DCM_ora_18653_bisal.trc
复制

...
LOCK TABLE "T_PK_02" IN SHARE MODE  NOWAIT
...
alter table t_pk_02 add c
...
update con$ ...
...
insert into con$ ...
...
CREATE UNIQUE INDEX "BISAL"."PK_ID_02" on "BISAL"."T_PK_02"("ID") NOPARALLEL
复制

SQL> create table t_pk_03 as select * from t_pk_01;
Table created.
复制

SQL> select table_name, constraint_name, constraint_type from user_constraints where table_name='T_PK_03';
no rows selected

SQL> select table_name, index_name, uniqueness from user_indexes where table_name='T_PK_03';
no rows selected
复制

SQL> create unique index pk_id_03 on t_pk_03(id) online;
Index created.

SQL> alter table t_pk_03 add constraint pk_id_03 primary key (id);
Table altered.
复制

SQL> create unique index pk_id_03 on t_pk_03(id) tablespace xxx;
Index created.
复制