Oracle约束的定义及管理

一、约束类型

非空约束：not null

唯一性约束：unique

主键约束：primary key

外键约束：foreign key

检查性约束：check

二、定义约束

clazz表：cid、cname

create table clazz(
       cid number(4) constraint clazz_cid_pk primary key,
       cname varchar(10)
);

student表：sid、scard、sname、sgender、cid

列级约束（没有指定约束名）：

create table student(
       sid number(4) primary key ,
       scard varchar2(10) unique,
       sname varchar2(10) not null,
       sgender varchar2(6) check (sgender in ('male','female')),
       cid number(4) references clazz(cid)
);

列级约束（指定约束名）：

create table student(
       sid number(4) constraint stu_sid_pk primary key,
       scard varchar2(10) constraint stu_scard_uk unique,
       sname varchar2(10) constraint stu_sname_nn not null,
       sgender varchar2(6) constraint stu_sgender_ck check (sgender in ('male','female')),
       cid number(4) constraint stu_cid_fk references clazz(cid)
);

表级约束：

（not null只能用于列级约束）

create table student(
       sid number(4),
       scard varchar2(10),
       sname varchar2(10) constraint stu_sname_nn not null,
       sgender varchar2(6),
       cid number(4),
       constraint stu_sid_pk primary key(sid),
       constraint stu_scard_uk unique(scard),
       constraint stu_sgender_ck check (sgender in ('male','female')),
       constraint stu_cid_fk foreign key(cid) references clazz(cid)
);

三、追加约束

alter table student modify (sname not null);

alter table student add constraint stu_sid_pk primary key(sid);

alter table student add constraint stu_scard_uk unique(scard);

alter table student add constraint stu_sgender_ck check (sgender in ('male','female'));

alter table student add constraint stu_cid_fk foreign key(cid) references clazz(cid);

四、删除约束

alter table student modify (sname null);

drop constraint stu_sid_pk;

drop constraint stu_scard_uk;

drop constraint stu_sgender_ck;

drop constraint stu_cid_fk;

五、约束启用与禁用

alter table student disable constraint stu_sid_pk;

alter table student enable constraint stu_sid_pk;

六、查询约束

通过数据字典user_constraints、user_cons_columns可查询约束

select constraint_name, constraint_type, status 
from user_constraints where table_name = 'STUDENT';

select constraint_name, column_name 
from user_cons_columns where table_name = 'STUDENT';