Oracle 学习第三篇

Day03-Oracle约束
学习目标：
 什么是约束
 约束的功能
 约束分类（重点）
 案例商店售货系统（掌握）
 约束维护
1.什么是约束？
1.1约束定义
约束：是强加在表上的规则或条件。确保数据库满足业务规则。保证数据的完整性。当对表进行 DML 或
DDL 操作时，如果此操作会造成表中的数据违反约束条件或规则的话，系统就会拒绝执行这个操作。约束
可以是列一级别的 也可以是表级别的。定义约束时没有给出约束的名字，ORACE 系统将为该约束自动生
成一个名字，其格式为 SYS_Cn，其中 n 为自然数(强烈建议各位在创建表或增加约束时，给约束定义名
称。).
1.2约束功能
约束的功能：实现一些业务规则，防止无效的垃圾数据进入数据库，维护数据库的完整性(完整性指正确
性与一致性)。从而使数据库的开发和维护都更加容易。

2.约束分类
约束分为： 非空（NOT NULL）约束、 唯一（UNIQUE）约束、主键（PRIMARY KEY）约
束、外键（FOREIGN KEY）约束、条件（CHECK）约束。
2.1 not null非空
非空（NOT NULL）约束：顾名思义，所约束的列不能为 NULL值。否则就会报错

举例：
create table user1(id number,name varchar2(30) not null);
insert into user1 values(001,'');//会报错

2.2 unique 唯一
唯一（UNIQUE）约束：在表中每一行中所定义的这列或这些列的值都不能相同。必须
保证唯一性。否则就会违法约束条件。
用于指定列的值不能重复，可以为 null
举例：
create table user2(id number unique,name varchar2(30));
insert into user2 values(1,111);//id 输入重复的值是会报错
注意：oracle 中 unique可以为 null，而且允许多行为 null
2.3 primary key主键
主键（PRIMARY KEY）约束：唯一的标识表中的每一行，不能重复，不能为空。 创
建主键或唯一约束后，ORACLE 会自动创建一个与约束同名的索引（UNIQUENES 为
UNIQUE 唯一索引） 。需要注意的是：每个表只能有且有一个主键约束。

举例：
create table user3(id number primary key,name varchar2(30));
insert into user3 values(1,111);
insert into user3 values(1,111);//报错，唯一性
insert into user3 values(null,111);//报错，不能为 null

特别说明 primary key 与 unique 的区别：
1、一张表可以有多个 unique(唯一)约束；
2、一张表只能有一个主键；
3、设置为主键的列不能有 null 值；
2.4 foreign key外键 references
外键（FOREIGN KEY）约束：用来维护从表（Child Table）和主表（Parent Table）之
间的引用完整性.能够维护数据库的数据一致性，数据的完整性。防止错误的垃圾数据入库；
用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必需
具有主键约束或是 unique 约束，当定义外键约束后，要求外键列数据必需在
主表的主键列存在或是为null
create table class(id number primary key,name varchar2(32));
create table stus(id number primary key,
name varchar2(36) not null,
classid number references class(id)
);

特别说明：froeign key 外键的细节
1、外键指向主键列；
2、外键可以指向 unique列；
3、建表时先建主表，再建从表；删除表先删从表，再删主表。
4、外键列属性值要与主键或 unique列属性值的类型保持一致
5、外键列的值，必需在主键列中存在。但外键列的值允许为 null

2.5 check 检查
条件（CHECK）约束：表中每行都要满足该约束条件。条件约束既可以在表一级定义
也可以在列一级定义。在一列上可以定义任意多个条件约束。

举例：
create table user4(id number primary key,
sal number check(sal>=1000 and sal<=2000),
sex char(2) check(sex in('男','女')));
insert into user4 values(1,1000,'男');//sal列的值不满足 1000至 2000，报错。

3.案例：（商店售货系统）（掌握创建约束）
商店售货系统表设计案例：

现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：
商品 goods 表：
商品号 goodsId（主键） ，商品名 goodsName（非空） ，
单价 price（大于零） ，商品类别 category，供应商 provider；
客户 customer:
客户号 customerId（主键） ，姓名 name（非空） ，住址 address，
邮箱 email，性别 sex（默认男，check） ，身份证 cardId（唯一） ；
购买 purchase：
客户号 customerId（外键） ，商品号 goodsId（外键） ，
购买数量 nums（1到 30个） ；

请用 SQL语言完成下列功能：
1. 建表，在定义中要求声明：
(1).每个表的主外键；
(2).客户的姓名不能为空值；
(3).单价必须大于 0，购买数量必须在 1 到 30 之间；
(4).邮箱不能够重复；
(5).客户的性别必须是 男 或者 女，默认是男；

 商品表：
商品 goods表：
商品编号：goodsId 商品名称：goodsName
单价：price 商品类别：category
供应商：provider
代码：
create table goods(goodsId number primary key,
goodsName varchar2(36),
price number check(price>0),
category varchar2(64),
provider varchar2(64)
);

 客户表：
客户 customer表：
客户编号：customerId 姓名：name，
住址：address 邮箱：email，
性别：sex 身份证：idCard
代码：
create table customer(customerId number primary key,
name varchar2(32) not null,
address varchar2(64),
email varchar2(64) unique,
sex char(2) default'男' check(sex in('男','女')),
idCard varchar2(20)
);

 购买表：
购买 purchase表：
客户编号：customerId 商品编号：goodsId
购买数量：nums
代码：
create table purchase(customerId number references customer(customerId),
goodsId number references goods(goodsId),
nums number check(nums>=1 and nums<=30),
primary key(customerId,goodsId)
);

4.维护约束（掌握）
4.1修改约束
如果在建表时忘记建立必要的约束， 则可以在建表后使用 alter table命令为表增加约束；
注意：
1）增加not null 约束使用modify（因为字段（列）默认都是可以为空）
2）增加其它四种约束使用add。

 添加或修改not null约束
语法：
alter table 表名 modify 字段名 not null;

 添加或修改unique(唯一)、primary key(主键)、foreign key(外键)和check(检查)约
束
语法：
alter table 表名 add constraint 约束名 约束种类(字段);


 修改售货系统表设计方案
1、增加 purchase表主键；
alter table purchase add constraint PK_PURCHASE_PID primary key (cId,gId);
2、客户的姓名不能为空值；--增加商品名也不能为空
alter table goods modify goodsName not null;//添加非空约束要用 modify
3、邮箱不能够重复；--增加身份证也不重复
alter table customer add constraint unique_cardId unique(cardId);
alter table customer add constraint UK_EMAIL unique(email);
4、增加客户的住址只能是(伊滨区，老城区，洛龙区)
alter table customer add constraint check_address check(address in('伊滨区','老城区','洛龙区'));

4.2删除约束
当不再需要某个约束时，可以删除。
删除约束基本语法：
alter table 表名 drop constratint 约束名称;

约束名称指的是：一个表的每一个约束都对应一个名称。约束名称用户没有设置时，系
统会自动分配一个名称。

特别说明：
在删除主键约束的时候，可能有错误，
比如：alter table 表名 drop primary key;
这是因为如果在两张表存在主从关系，那么在删除主要的主键约束时，
必需带上 cascade 选项。
基本语法：
alter table 表名 drop primary key cascade;
4.3约束命名规范
约束名称：建议自己定义一套命名规则，否则使用系统生成的约束名，很难能把它和对应的
表、字段联系起来。
约束命名规则：
非空约束： NN_表名_列名
唯一约束： UK_表名_列名
主键约束： PK_表名
外键约束： FK_表名_列名
条件约束： CK_表名_列名
4.4显示约束信息（了解）
1）显示表约束信息
通过查询数据字典视图 user_constraints，可以显示当前用户所有的约束的信息。
语法：
select constraint_name, constraint_type, status, validated
from user_constraints
where table_name = '表名';
注意：表名要大写
2）当然也有更容易的方法，直接用 pl/sql developer查看即可。
4.5表级定义 列级定义（了解）
 列级定义
列级定义：是在定义列的同时定义约束。

举例：如果在 department 表定义主键约束
create table department(
dept_id number(12) constraint pk_department primary key,
name varchar2(12), loc varchar2(12));
 表级定义
表级定义：指在定义了所有列后，再定义约束。
举例：建立 employee 表时定义主键约束和外键约束为例：
create table employee(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)
);
特别说明：not null 约束不可以出现在表级定义中，not null 约束只能在列级上定义。
一般情况下，我们使用列级定义即可。但是如果遇到定义复合主键(两列一起被定义为
主键)时，需要用到表级定义。
4.6联合主键（了解）
举例，将 id与 name定义为复合主键：
create table test(id number,
name varchar2(64),nums number,
constraint pk_id_name primary key(id,name));

特别说明：不推荐大家使用复合主键。

转载于:https://www.cnblogs.com/2895428004qq/p/7798284.html