一 作用
确保表当中每一行数据的唯一性
二 主键特点
非空 唯一
一张表中只能设计一个主键约束
主键约束可以由多个字段构成，这种情况被称为联合主键或复合主键
三 主键约束
1、在创建表时设置主键约束
CREATE TABLE tabl_name(
column_name datatype PRIMARY_KEY,...
)
SQL> create table userinfo_p2(id number(6,0) primary key,3 username varchar2(20),4 userpwd varchar2(20));表已创建。SQL> desc userinfo_p名称是否为空?类型----------------------------------------------------------------------------- ID NOT NULL NUMBER(6) USERNAME VARCHAR2(20) USERPWD VARCHAR2(20)
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_USERNAMESQL>select constraint_name from user_constraints where table_name='USERINFO_P';CONSTRAINT_NAME------------------------------SYS_C0011056
2、在修改表时添加主键约束
ADD CONSTRAINT constraint_name
PRIMARY KEY(column_name1,...);
SQL> alter table userinfo2 add constraint pk_id primary key(id);表已更改。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';CONSTRAINT_NAME------------------------------PK_ID
3、更改约束名称
RENAME CONSTRAINT old_name
TO new_name
2 rename constraint pk_id to new_pk_id;SQL>select constraint_name from user_constraints where table_name='USERINFO';CONSTRAINT_NAME------------------------------NEW_PK_ID
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
2 drop constraint new_pk_id;未选定行
DROP PRIMARY KEY[CASCARDE]
CASCARDE用于级联删除。
SQL> desc userinfo_p USERNAME VARCHAR2(20) USERPWD VARCHAR2(20)SQL> alter table userinfo_p2 drop primary key;表已更改。 ID NUMBER(6) USERPWD VARCHAR2(20) （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
