维护数据的完整性

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

 

.约束

约束用于确保数据库数据满足特定的商业规则。

在oracle中，约束包括：not null  ;  unique  ; primary key  ;  foreign key ;  check 五种

 

.not null

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

 

.unique

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

 

.primary key

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

一张表最多只能有一个主键，但是可以有多个unique。

 

.foreign key

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

 

.check

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

 

.商店售货系统表设计

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

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

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

要求：

建表，在定义中声明：

每个表的主外键；

客户的姓名不能为空值；

单价必须大于0，购买必须在1到30之间；

电邮不能够重复；

客户性别必须是男或女，默认为男；

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(30) not null,

address varchar2(50),

email varchar2(30) unique,

sex char(2) default ‘男’ check (sex in (‘男’,’女’)),

cardId char(18) unique);

 

create table purchase(customerId char(8) references customer(customerId),

goodsId char(8) references goods(goodsId),

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

  

.商店售货系统表设计2

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

每个表的主外键；

客户的姓名不能为空值；--增加商品名也不能为空

单价必须大于0，购买必须在1到30之间；

电邮不能够重复；--增加身份证也不重复

客户性别必须是男或女，默认为男；

增加客户的住址只能是’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,

 

alter table goods modify goodsName not null;

 

alter table customer add constraint cardunique unique(cardId);

 

alter table customer add constraint addresscheck  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=’约束名’;

 

.列级定义

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

如在department表定义主键约束

create table department4

(dept_id number(2) constraint pk_department primary key,

name varchar2(12),

loc varchar2(12));

 

.表级定义

是在定义了所有列后，再定义约束。要注意：not null约束只能在列级上定义

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));

 

管理索引

索引用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。

 

.单列索引 基于单个列所建立的索引

create index索引名 on表名(列名)

create index nameindex on customer(name);

 

.复合索引基于两列或多列的索引。在同一张表上可以有多个索引，但要求列的组合必须不同，如：

create index emp_idx1 on emp (ename,job);

create index emp_idx1 on emp (job,ename);

 

.使用原则

在大表上建立索引才有意义

在where子句或连接条件上经常引用的列上建立索引

索引的层次不要超过4层

 

.缺点

建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引

更新数据的时候，系统必须要额外的时间来同时对索引进行更新，以维持数据和索引的一致性

 

.显示表的所有索引

通过dba_indexs和 user_indexs可以显示索引信息。其中dba_indexs用于显示数据库所有索引信息，而user_indexs用于显示当前用户的索引信息。

select index_name,index_type from user_indexs where table_name=’表名’;

.显示索引列

通过user_ind_columns,可以显示索引对应的列的信息

select table_name,column_name from user_ind_columns where index_name=’IND_ENAME’;