创建一个学生表和院系表:院系表为主表,学生表为从表
create table student
(sid number(8,0),
name varchar2(10),
sex char(2),
birthday date,
email varchar2(20)
depid number(5,0)
);
create table departmment
(depid number(5,0) primary key,
depname varchar2(20)
);
约束类型 | 创建表时添加约束 | 修改表时添加约束 | 禁用或激活约束 | 删除约束 |
主键约束(primary key) | 1.通过表级约束添加: create table student(sid number(8,0), name varchar2(10), constraint sid_pk primary key(sid) 2.通过列级约束添加: (1)不指定约束名,自动生成 create table student ); (2)指定约束名 create table student ); 若主键涉及多列,则只能通过 表级约束添加
| alter table student add constraint sid_pk primary key(sid); | 禁用/启用: alter table student disable /ennable constraint sid_pk; | alter table student drop connstraint sid_pk; |
非空约束(not null) | 只能通过列级约束添加: (1)不指定约束名,自动生成,可在check中查看 create table student ); (2)指定约束名 create table student );
| alter table student modify( name varchar(20) not null); | 禁用/启用: alter table student disable/enable constraint nnn_name; | alter table student modify( name varchar(20) null); |
唯一性约束(unique) | 1.通过表级约束添加: create table student(sid number(8,0), name varchar2(10), email varchar2(20), constraint email_uq unique(email) 2.通过列级约束添加: (1)不指定约束名,自动生成 create table student email varchar2(20) unique ); (2)指定约束名 create table student email varchar2(20) constraint email_uq unique ); 若唯一性约束涉及多列,则只能通过表级约束添加
| alter table student add constraint email_uq unique(email); | 禁用/启用: alter table student disable/enable constraint email_uq; | alter table student drop connstraint email_uq; |
检查约束(check) | 1.通过表级约束添加: create table student(sid number(8,0), name varchar2(10), email varchar2(20), constraint sex_ck check(sex='男' or sex='女') ); 2.通过列级约束添加: (1)不指定约束名,自动生成 create table student email varchar2(20), ); (2)指定约束名 create table student email varchar2(20) ); | alter table student add constraint sex_ck check(sex='男' or sex='女'); | 禁用/启用: alter table student disable/enable constraint sex_ck; | alter table student drop connstraint sex_ck; |
外键约束(foreign key) | 1.通过表级约束添加: create table student(sid number(8,0), name varchar2(10), email varchar2(20), depid number(5,0), constraint depid_fk foreign key(depid) references department (depid) ); 2.通过列级约束添加: (1)不指定约束名,自动生成 create table student email varchar2(20), depid number(5,0)references department (depid) );
| alter table student add constraint depid_fk foreign key(depid) references department(depid) | 禁用/启用: alter table student disable/enable constraint depid_fk; | alter table student drop constraint depid_fk; |