Oracle之主键的创建、添加、删除操作

最新推荐文章于 2023-04-06 22:29:38 发布

weixin_30525825

最新推荐文章于 2023-04-06 22:29:38 发布

阅读量123

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/Richardzhu/p/3470929.html

版权

一、创建表的同时创建主键约束

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
------------------------------ ------------------------------ ------------------------------ ------------------------