Oracle之主键的创建、添加、删除操作
一、创建表的同时创建主键约束
1.1、无命名
SQL> create table jack (id int primary key not null,name varchar2(20));
Table created
SQL> select table_name,index_name from user_indexes where table_name='JACK';
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
JACK SYS_C0011100
1.2、有命名
SQL> create table jack (id int ,name varchar2(20),constraint ixd_id primary key(id));
Table created
SQL> select table_name,index_name from user_indexes where table_name='JACK';
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
JACK IXD_ID
二、向表中添加主键约束
SQL> create table jack as select * from dba_objects;
Table created
SQL> desc jack;
Name Type Nullable Default Comments
-------------- ------------- -------- ------- --------
OWNER VARCHAR2(30) Y
OBJECT_NAME VARCHAR2(128) Y
SUBOBJECT_NAME VARCHAR2(30) Y
OBJECT_ID NUMBER Y
DATA_OBJECT_ID NUMBER Y
OBJECT_TYPE VARCHAR2(19) Y
CREATED DATE Y
LAST_DDL_TIME DATE Y
TIMESTAMP VARCHAR2(19) Y
STATUS VARCHAR2(7) Y
TEMPORARY VARCHAR2(1) Y
GENERATED VARCHAR2(1) Y
SECONDARY VARCHAR2(1) Y
NAMESPACE NUMBER Y
EDITION_NAME VARCHAR2(30) Y
SQL> alter table jack add constraint pk_id primary key(object_id);
Table altered
SQL> select table_name,index_name from user_indexes where table_name='JACK';
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
JACK PK_ID
----另外当索引创建好以后再添加主键的效果：
SQL> create table jack as select * from dba_objects;
Table created
SQL> create index ind_object_id on jack(object_id);
Index created
SQL> select table_name,index_name from user_indexes where table_name='JACK';
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
JACK IND_OBJECT_ID
SQL> desc jack;
Name Type Nullable Default Comments
-------------- ------------- -------- ------- --------
OWNER VARCHAR2(30) Y
OBJECT_NAME VARCHAR2(128) Y
SUBOBJECT_NAME VARCHAR2(30) Y
OBJECT_ID NUMBER Y
DATA_OBJECT_ID NUMBER Y
OBJECT_TYPE VARCHAR2(19) Y
CREATED DATE Y
LAST_DDL_TIME DATE Y
TIMESTAMP VARCHAR2(19) Y
STATUS VARCHAR2(7) Y
TEMPORARY VARCHAR2(1) Y
GENERATED VARCHAR2(1) Y
SECONDARY VARCHAR2(1) Y
NAMESPACE NUMBER Y
EDITION_NAME VARCHAR2(30) Y
SQL> alter table jack add constraint pk_id primary key(object_id);
Table altered
SQL> desc jack;
Name Type Nullable Default Comments
-------------- ------------- -------- ------- --------
OWNER VARCHAR2(30) Y
OBJECT_NAME VARCHAR2(128) Y
SUBOBJECT_NAME VARCHAR2(30) Y
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER Y
OBJECT_TYPE VARCHAR2(19) Y
CREATED DATE Y
LAST_DDL_TIME DATE Y
TIMESTAMP VARCHAR2(19) Y
STATUS VARCHAR2(7) Y
TEMPORARY VARCHAR2(1) Y
GENERATED VARCHAR2(1) Y
SECONDARY VARCHAR2(1) Y
NAMESPACE NUMBER Y
EDITION_NAME VARCHAR2(30) Y
SQL> select table_name,index_name from user_indexes where table_name='JACK';
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
JACK IND_OBJECT_ID
三、修改主键约束
3.1、禁用/启用主键
SQL> select a.table_name,a.index_name,b.constraint_name,b.constraint_type,b.status from user_indexes a,user_constraints b where b.table_name='JACK'and a.table_name=b.table_name;
TABLE_NAME INDEX_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
------------------------------ ------------------------------ ------------------------------ --------------- --------
JACK PK_ID PK_ID P ENABLED
SQL> alter table jack disable primary key;
Table altered
SQL> select a.table_name,a.index_name,b.constraint_name,b.constraint_type,b.status from user_indexes a,user_constraints b where b.table_name='JACK'and a.table_name=b.table_name;
TABLE_NAME INDEX_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
------------------------------ ------------------------------ ------------------------------ --------------- --------
SQL> alter table jack enable primary key;
Table altered
SQL> select a.table_name,a.index_name,b.constraint_name,b.constraint_type,b.status from user_indexes a,user_constraints b where b.table_name='JACK'and a.table_name=b.table_name;
TABLE_NAME INDEX_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
------------------------------ ------------------------------ ------------------------------ --------------- --------
JACK PK_ID PK_ID P ENABLED
3.2、重命名主键
SQL> select a.table_name,a.index_name,b.constraint_name,b.constraint_type,b.status from user_indexes a,user_constraints b where b.table_name='JACK'and a.table_name=b.table_name;
TABLE_NAME INDEX_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
------------------------------ ------------------------------ ------------------------------ --------------- --------
JACK PK_ID PK_ID P ENABLED
SQL> alter table jack rename constraint pk_id to pk_jack_id;
Table altered
SQL> select a.table_name,a.index_name,b.constraint_name,b.constraint_type,b.status from user_indexes a,user_constraints b where b.table_name='JACK'and a.table_name=b.table_name;
TABLE_NAME INDEX_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
------------------------------ ------------------------------ ------------------------------ --------------- --------
JACK PK_ID PK_JACK_ID P ENABLED
四、删除表中已有的主键约束
4.1、无命名
----先利用user_cons_columns表查得主键名：
SQL> select owner,constraint_name,table_name,column_name from user_cons_columns where table_name = 'JACK';
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------ ----------------
JACK SYS_C0011105 JACK ID
SQL> select table_name,index_name from user_indexes where table_name='JACK';
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
JACK SYS_C0011105
SQL> alter table jack drop constraint SYS_C0011105;
Table altered
4.2、有命名
SQL> select owner,constraint_name,table_name,column_name from user_cons_columns where table_name = 'JACK';
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------
JACK IXD_ID JACK ID
SQL> alter table jack drop constraint IXD_ID;
Table altered
SQL> select owner,constraint_name,table_name,column_name from user_cons_columns where table_name = 'JACK';
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------
We only live once, and time just goes by.
