Oracle主键约束

一作用

确保表当中每一行数据的唯一性

二主键特点

非空唯一

一张表中只能设计一个主键约束

主键约束可以由多个字段构成，这种情况被称为联合主键或复合主键

三主键约束

1、在创建表时设置主键约束

CREATE TABLE tabl_name(

column_name datatype PRIMARY_KEY,...

)

CONSTRAINT constraint_name

PRIMARY KEY(column_name1,...)

sql> create table userinfo_p1(
2 id number(6,0),monospace; margin: 8px 0px;" class="language-sql">4 userpwd varchar2(20),
5 constraint pk_id_username primary key(id,username));
表已创建。
sql> desc userinfo_p1;
名称是否为空?类型
-----------------------------------------------------------------------------
ID NOT NULL NUMBER(6)
USERNAME NOT NULL VARCHAR2(20)
USERPWD VARCHAR2(20)
sql> desc user_constraints
名称是否为空?类型
-----------------------------------------------------------------------------
OWNER VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
sql>select constraint_name from user_constraints where table_name='USERINFO_P1';
CONSTRAINT_NAME
------------------------------
PK_ID_USERNAME
sql>select constraint_name from user_constraints where table_name='USERINFO_P';
CONSTRAINT_NAME
------------------------------
SYS_C0011056

2、在修改表时添加主键约束

ADD CONSTRAINT constraint_name

PRIMARY KEY(column_name1,...);

3、更改约束名称

RENAME CONSTRAINT old_name

TO new_name

4、删除主键约束

DISABLE|ENABLE CONSTRAINT constraint_name

2 disable constraint new_pk_id;
sql>select constraint_name,status from user_constraints where table_name='USERINFO';
CONSTRAINT_NAME STATUS
--------------------------------------
NEW_PK_ID DISABLED

DROP CONSTRAINT constraint_name

DROP PRIMARY KEY[CASCARDE]

CASCARDE用于级联删除。

原文链接：https://www.f2er.com/oracle/208183.html

猜你在找的Oracle相关文章