oracle 11g 学习笔记 10_31(2)_维护数据的完整性

对于有海量数据的表，建立索引查询速度比没有建立索引的要快得多。

维护数据的完整性

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

一、约束

约束用于确保数据库数据满足特定的商业规则，在oracle中，约束包括：not null、unique、primary key、foreign key和check五种。
.not null(非空)
如果在列定义了 not null，那么当插入数据时，必须为列提供数据。
.unique（唯一）
当定义了唯一约束后，该列值是不能重复的，但是可以为null
.primary key(主键)
用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能null。
需要说明的是：一张表最多只能有一个主键，但是可以有多个unqiue约束。
.foreign key(外键)
用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主键列存在或是为null
.check
用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-200之间，弱国不在1000-2000之间就会提示出错。

*案例
商店售货系统表设计案例?   
现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：商品goods
（商品号goodsId，商品名 goodsName，单价 unitprice，商品类别category，
供应商provider）；  
客户customer（客户号customerId，姓名name，住在address，电邮email，
性别sex，身份证cardId）；  
购买purchase（客户号customerId，商品号goodsId，购买数量nums）；  
请用SQL语言完成下列功能：  
1. 建表，在定义中要求声明：  
(1). 每个表的主外键；  
(2). 客户的姓名不能为空值；  
(3). 单价必须大于0，购买数量必须在1到30之间；  
(4). 电邮不能够重复；  
(5). 客户的性别必须是 男 或者 女，默认是男； 

create table GOODS
(
  goodsid   CHAR(8) primary key,--设置主键
  goodsname VARCHAR2(30),
  unitprice NUMBER(10,2) check(unitprice > 0),
  category  VARCHAR2(8),
  provider  VARCHAR2(30)
);
create table CUSTOMER
(
  customerid CHAR(8) primary key,--设置主键
  name       VARCHAR2(50) not null,
  address    VARCHAR2(50),
  email      VARCHAR2(50) unique,
  sex        CHAR(2) default '男' check(sex in('男','女')), --一个char能存半个汉字，两位char能存一个汉字。
  cardid     CHAR(18)
);
create table PURCHASE
(
  customerid CHAR(8) references customer(customerId),--指向customer表的customerId
  goodsid    CHAR(8) references goods(goodsId),
  nums       NUMBER(5) check(num between 1 and 30)
);

*增加约束
商店售货系统表设计案例（2）?   
如果在建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束。但是要注意：增加not null约束时，需要使用modify选项，而增加其它四种约束使用add选项。  
1. 增加商品名不能为空

alter table goods modify goodsName not null;

2. 增加身份证不能重复

alter talbe customer add constraint cardIdUnique unique(cardId);

其中，add constraint cardIdUnique 表示增加一个名为cardIdUnique的约束，constraint表示约束的意思。
3. 增加客户的住址只能是'天河'、'越秀'、'海珠'、'番禺'、'荔湾'、'从化'

alter table customer add constraint addrCheck check (address in('天河','海珠','越秀','荔湾','从化'));

*删除约束
当不再需要某个约束时，可以删除
alter table 表名 drop constraint 约束名称;
在删除主键约束的时候，可能有错误，比如
alter table 表名 drop primary key;
这是因为如果在两张表存在主从关系，那么在删除主表的主键约束时，必须带上cascade选项，
alter table 表名 drop primary key cascade;

*显示约束信息
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这个工具查看

二、表级定义和列级定义

*列级定义
列级定义是在定义列的同时定义约束

create table PURCHASE
(
  customerid CHAR(8) references customer(customerId),--指向customer表的customerId
  goodsid    CHAR(8) references goods(goodsId),
  nums       NUMBER(5) check(num between 1 and 30)
);

*表级定义
表级定义是指在定义了所有列之后，再定义约束，需要注意的是：not null约束只能在列级上定义
例：

create table GOODS
(
  goodsid   CHAR(8),
  goodsname VARCHAR2(30),
  unitprice NUMBER(10,2),
  category  VARCHAR2(8),
  provider  VARCHAR2(30)，
constraint pk_goodsid primary key (goodsId),--设置主键
constraint chk_price check(unitprice > 0) --价格大于零
);

这两种定义实质上是没有区别的，要是用列级定义，由于没有给约束的一个名字，系统会随机地给约束一个名字。这个就是区别。

三、索引

*介绍
索引适用于加速数据存取的数据对象，合理的使用索引可以大大降低i/o次数，从而提高数据访问性能，索引有很多种：
为什么添加索引后，会加快查询速度？
*创建索引
.单列索引是基于单个列所建立的索引，比如
create index 索引名 on 表名(列名)
.复合索引
符合索引是基于两列或是多列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同，比如

create index emp_idxl on emp(ename,job);
create index emp_idxl on emp(job,ename);

*索引使用原则
1）在大表上建立索引才有意义
要是表的数据小，那么就很没有意义了，即浪费了我们做索引的时间，还占用了空间。
2）在where子句或是连接条件上经常引用的列上建立索引
3）索引的层次不要超过4层


*索引缺点分析
1）建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引
2）更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。
不恰当的索引不但无济于事，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。