【Oracle11g】05_完整性约束

目录

• 1.数据的完整性简介
• 2.维护完整性的5种方式:not null、unique、check、primary key、foreign key
  • 2.1 案例说明
    • 2.1.1 创建not null约束
    • 2.1.2 创建check约束
    • 2.1.3 创建unique约束
    • 2.1.4 default的使用
    • 2.1.5 主键primary key与外键foregin key
• 3.创建主键的五种方式
• 4.综合实战演练
• 5.删除约束
• 6.禁用约束

1.数据的完整性简介

就是正确性、准确性，包含三种：实体完整性、参照完整性、用户自定义完整性。Oracle中主要通过约束、触发器、过程函数实现的。
以下举例子说明：

表	字段
员工表	员工编号、员工名称、部门编号
部门表	部门编号、部门名称
学生表	学生编号、学生姓名、出生日期、当前日期

实体完整性:员工表的员工编号是主键列，那么员工编号这一列的值不可能错误，显而易见，员工编号不能为负数，这就是实体完整性。
参照完整性：员工表的外键是部门表，员工表中的部门编号必须是来自部门表的，这就是参照完整性。
用户自定义完整性：对于学生表来说，任何一个学生的出生日期都是小于当前日期的，这就是自定义完整性。

2.维护完整性的5种方式:not null、unique、check、primary key、foreign key

• not null例如学生的姓名；unique例如学生的学号；primary key主键、foreign key外键、check可以按照用户要求，进行自动检查。
• unique不可以重复，但是可以为空。
• primary key不可以重复，也不能为空。
• 一个表只能一个primary key，却可以多个unique。
• not null只能在列内定义，其他4种约束可以在列定义结束后，在表内定义

2.1 案例说明

2.1.1 创建not null约束

create table custInfo1(cust_no number not null,cust_name varchar2(20));
-- 查看创建的约束
 select u.constraint_name,u.constraint_type,u.table_name,u.index_name  from user_constraints u;

从下图可以看到刚刚给表custinfo1创建的约束

2.1.2 创建check约束

create table custInfo2(cust_no number not null check (cust_no>0 and cust_no<1000),cust_name varchar2(20));

select u.constraint_name,u.constraint_type,u.table_name,u.index_name  from user_constraints u;

通过下图可以看到表custinfo2存在2个约束，一个是not null 一个是check。

同时，也可以通过表user_cons_columns查看约束与列的对应关系；

select * from user_cons_columns;

2.1.3 创建unique约束

-- 现在有员工信息表，部门编号与部门内部编号才能唯一确定一条记录，所以根据部门编号和员工内部编号创建唯一性约束
create table empInfo(dept_no number not null, emp_no number, cust_name varchar2(20), unique(dept_no,emp_no));

查看创建的唯一性约束后发现，当为列创建唯一性约束的时候，同时也会自动创建索引。

2.1.4 default的使用

default适用于没有向列显示的指定数值的情况

-- 例如：创建一个员工表，指定员工的默认工资为2000
 create table sal(empno number,ename varchar2(20),salary number(8,2) default 2000);

-- 第一种插入
insert into sal values(1,'AA',default);
-- 第二种插入
insert into sal(empno,ename) values(2,'BB');

查询结果：

2.1.5 主键primary key与外键foregin key

create table student(stu_no number constraint pk_stu_no primary key, stu_name varchar2(40));
-- 表city上的stu_no参照了student表的stu_no列，此处的外键名是由系统自动命名
create table city(stu_no number, addr varchar2(20), foreign key(stu_no) references student(stu_no));

3.创建主键的五种方式

-- 在列上定义主键，Oracle自己起名
create table user1(id number primary key, name varchar2(20));

-- 在列上定义主键，自定义名字
create table user2(id number constraint pk_user2 primary key, name varchar2(20));

-- 在表上定义主键，Oracle自己起名
create table user3(id number, name varchar2(20),primary key(id));

-- 在表上定义主键，自定义名字
create table user4(id number, name varchar2(20),constraint pk_id primary key(id));

-- 先建表，再加主键
create table user5(id number, name varchar2(20));
alter table user5 add constraints pk_user5 primary key(id);

4.综合实战演练

建立2个表，一个是stuInfo（学号sno，姓名sname，年龄sage，性别smale，系编号deptNo），一个是department（系编号deptNo，系名称deptName）。
要求：
每个表有主键。
stuInfo表建立外键。
学生的姓名不能为空。
学生的年龄要在18到50之间。
学生的性别必须是男女之一，默认是男。
stuInfo表的deptNo列，要参照department表的deptNo列。

实现代码：

-- stuInfo 创建
create table stuInfo(
sno number,
sname varchar2(20) not null , -- 学生的姓名不能为空。
sage number check(sage>18 and sage<50), -- 学生的年龄要在18到50之间。
smale char(2) default '男' check(smale='男' or smale='女'),  -- 学生的性别必须是男女之一，默认是男。
deptno varchar2(10),
constraints pk_stuInfo primary key(sno)
);

-- department创建
create table department(
deptno varchar2(10),
deptName varchar2(200),
constraints pk_department primary key(deptno)
);
-- 创建外键
alter table stuInfo add constraints fk_department foreign key(deptno) references department(deptno);

5.删除约束

语法：

alter table 表名 drop constraint 约束名;

如果删除一个主键被引用的表，则

drop table 表名 cascade constraints;

6.禁用约束

-- 禁用主键约束
alter table 表名 disable primary key;

-- 启用主键约束
alter table 表名 enable primary key;