Oracle学习笔记（十八）——维护数据的完整性

一、维护数据的完整性

数据的完整性：用于确保数据库数据遵从一定的商业和逻辑规则。在oracle中，数据的完整性，可以使用约束、触发器、应用程序（过程、函数）三种方法来实现。在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。

二、约束

1、约束：用于确保数据库数据满足特定的商业规则。在oracle中，约束包括：not null，unique，primary key，foreign key和check五种。

2、not null（非空）

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

3、unique

• 当定义了唯一约束后，该列值不可重复，但是可以为null。

4、primary key

• 用于唯一标示镖行的数据，当定义主键约束后，该列不但不能重复，而且不能为null。
• 说明：一张表最多只能有一个主键，但是可以有多个unique约束。

5、foreign key

• 用于定义主表和从表之间的关系。
• 外键约束要定义在从表上，主表则必须具有主键约束或是unique约束。
• 当定义外键约束后，要求外键列数据必须在主表的逐渐咧存在或是为null。

6、check

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

7、列级定义：在定义咧的同时定义约束。

• SQL> create table department(
    2  dept_id number(2) constraint pk_department primary key,
    3  name varchar2(12),
    4  loc varchar2(12));

8、表级定义：在定义了所有列后，再定义约束。

• 注意：not null 约束只能在列级上定义。
• SQL> create table employee(
    2  emp_id number(4),
    3  name varchar2(15),
    4  dept_id number(2), 
    5  constraint pk_employ3ee primary key (emp_id),
    6  constraint fk_department foreign key (dept_id)references department(dept_id));

三、商店收获系统表设计案例

现有一个商店的数据库，记录客户及其购物情况，有下面三个表组成：

1）商品goods（商品号goodsId，商品名goodsName，单价unitprice，商品类别category，供应商provider）

2）客户customer（客户号customerId，姓名name，住址address，电邮email，性别sex，身份证cardId）；

3）购买purchase（客户号customerId，商品号goodsId，购买数量nums）。

1、请用SQL语言完成下列功能：

• 每个表的主外键
• 客户的姓名不能为空值
• 单价必须大于0，购买数量必须在1到30之间
• 电邮不能够重复
• 客户的性别必须是男或者女，默认是男
• SQL>  create table goods (goodsId char(8) primary key,
    2  goodsName varchar2(30),
    3  unitprice number(10,2) check(unitprice>0),
    4  category varchar2(8),
    5  provider varchar2(30));
• SQL> create table customer(customerId char(8) primary key,
    2  name varchar2(30) not null,
    3  address varchar2(30),
    4   email varchar2(30) unique,
    5  sex char(2) default '男' check (sex in ('男','女')),
    6  cardId char(18));
• SQL> create table purchase(customerId char(8) references customer(customerid),
    2  goodsId char(8) references goods(goodsId),
    3  nums number(10) check (nums between 1 and 30));

2、如果在建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束，但是要注意：增加not null约束时，需要使用modify选项，而增加其它四中约束使用add选项。

• 每个表的主外键
• 客户的姓名不能为空值——增加商品名也不能为空
• 单价必须大于0，购买数量必须在1到30之间
• 电邮不能够重复——增加身份证也不重复
• 客户的性别必须是男或者女，默认是男
• 增加客户的住址只能是‘海淀’、‘朝阳’、‘东城’、‘西城’、‘通州’、‘崇文’
• SQL> alter table goods modify goodsName not null;
• SQL> alter table customer add constraint cardunique unique(cardId);
• SQL> alter table customer add constraint addresscheck check((address) in ('东城','朝阳','海淀','西城','通州','崇文'));

四、维护

1、删除约束

• alter table 表名 drop constraint 约束名称;
• 说明：在删除主键约束时，可能会有错误，比如：
• • alter table 表名 drop primary key;
  • 因为如果两张表存在主从关系，那么在删除主表的主键时，必须带上cascade选项，如：alter table 表名 drop primary key cascade;

2、显示约束信息

1）显示约束信息

• 通过查询数据字典视图user_constraints，可以显示当前用户所有的约束信息。
• select constraint_name,constraint_type,status,validated from user_constraints where table_name='表名';

2）显示约束列

• 通过查询数据字典视图user_cons_columns，可以显示约束所对应的表的列信息。
• select column_name,position from user_cons_columns where constraint_name='约束名';

3）用pl/sql developer查看。