一、约束

1、概念：

约束是表级的强制规定，有一下5种：

1）not null(非空)

如果在列上定义了not null，那么当插入数据时，必须为列提供数据；

保证列级不为空，只能定义在列级。若是不写的话就默认为空；

例如：name varchar2(20) not null,--保证列级不为空，只能定义在列级。若是不写的话就默认为空；

2）unique（唯一）

当定义了唯一约束后，该列值是不能重复的，但是可以为null。也就是unique对null不存在限制

可定义在表级或列级

例如：

email varchar2(50),

constraint  emp_email_uk unique(email);  

3）primary key（主键）

用于唯一的表示表行的数据，当定义主键约束后，该列不单不能重复而且不能为null

需要说明的是：一张表最多只能有一个主键，但是可以有多个unqiue约束；

可定义在表级或列级

例如：

goodsId char(8) primary key,

constraint  dept_id_pk primary key (department_id));

4）foreign key(外键)

用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，

当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null；

可定义在表级或列级

例如：

constraint fk_department foreign key (dept_id)

constraint  emp_dept_fk foregin key(department_id);

customerId char(8) references customer(customerId)

 

5）check

用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间，如果不在1000-2000之间就会提示出错；

         备注：如果不能指定约束名，Oracle server 自动按照SYS_cn的格式指定约束名；

 

2、表级约束和列级约束：

         1）列级约束只能作用在一个列上，列约束必须跟在列的定义后面，非空(not null) 约束只能定义在列上

         2）表级约束可以作用在多个列上（当然表级约束也可以作用在一个列上）表约束不与列一起，而是单独定义。

3、定义约束：

--创建商品表；

create table goods(goodsId char(8) primary key,--主键

goodsName varchar2(30),

unitprice number(10,2) check (unitprice > 0）, --10表示的是精度，2表示的是小数位意思是说总共为10位数，其中两位小数；

category varchar2(8),

provider varchar2(38));

 

--创建客户表：

create table customer（customerId, char(8), primary key,--主键

name varchar2(20) not null,--保证列级不为空，只能定义在列级。若是不写的话就默认为空；

address varchar2(50),

email varchar2(50), unique,--可定义在表级或列级

sex char(2) default '男' check (sex in ('男'，'女')) --2个字符，代表一个汉子

carId char(18) );

 

--创建购买表：

create table purchase (customerId char(8) references customer(customerId), --reference 引用或者指向

googsId char(8) references goods(goodsId),

nums number(10) check (nums between 1 and 30));

|实例1|

添加约束的语法：

如果在建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束，

但是要注意：增加not null约束时，需要使用modify选项，而增加其他四种约束使用add选项；

1）--增加商品名也不能为空

alter table goods modify goodsName not null;

2）--增加身份证也不能重复（即增加唯一约束）

alter table customer add constraint cardunique unique(cardId);

--add constraint表示增加约束，  cardunique约束的名字（可以随便取名）；

 

3)增加客户的住址只能是”海淀“，”朝阳“，“东城”，“西城”，“通州”，“崇文”；

alter table customer add constraint addresscheck (address in('东城','西城'))；

4)删除约束：

alter table 表明 drop constraint 约束名称

注意：在删除主键约束的时候可能有错误比如：

alter table 表明 drop primary key;

这是因为如果两张表存在主从关系，那么在删除主表的主键约束时，必须带上cascade选项

alter table 表明 drop primary key cascade； --cascade是重叠的意思

备注：删除后该表的主键所在字段的primary key 约束删除了，但是其他的约束信息依然存在。

5)查询约束信息：

通过查询数据字典视图user_constraints,可以显示当前用户所有的约束信息；--validate 确认，使生效；

select constraint_name,constraint_type,status,validated from user_constraints

where table_name='CUSTOMER';

6)查询定义的约束列：

通过查询数据字典视图user_cons_columns，可以显示约束所对应的表列信息；

select column_name,position

from user_cons_columns

where constraint_name='CARDID';

7)列级定义是在定义列的同时定义约束；

如在department 表定义主键约束

create table department4 (dept_id number(2) constraint pk_department primary key,

name varchar2(12),

loc varchar2(12));

-- pk_department主键约束名字

8)表级定义

是指在定义了所有列后，再定义约束，这里需要注意：not null约束只能在列级上定义；

以建立在employee2 表时定义主键约束和外键约束为例：

create table employee2

(emp_id number(4),name varchar2(15),dept_id number(2),

constraint pk_employee primary key (emp_id),

constraint fk_department foreign key (dept_id)

references department4 (dept_id));

--约束名字pk_employee

--约束名字fk_department

9）无效化约束

alter table employee2 disable constraint pk_employee;

10）激活约束

alter table employee2 enable constraint pk_employee;

11）建立外键约束时的级联删除问题:没调用成功

级联删除: 当父表中的列被删除时，子表中相对应的列也被删除

Create table dept (

       dept_id number  primary key not null,

       dept_name varchar2(20) not null

);

Create table emp (

       id number(3) primary key,

       name varchar2(25) unique,

       dept_id references dept(dept_id) on delete cascade);

drop

--alter table dept drop primary key cascade;

删除后dept.dept_id字段的主键约束将被删除，而非空约束依然存在；

--alter table dept drop column dept_id;

删除dept.dept_id后emp表中的dept_id也被删除

级联置空：当父表中的列被置空时，子表中相应的列也被置空

create table emp1(

       id number(3) primary key,

       name varchar2(25) unique,

       dept_id references dept (dept_id) on delete set null)