维护数据完整性

精选转载

spider1983 2014-04-17 09:31:16 博主文章分类：database

文章标签 Oracle 记录触发器 primary 文章分类 Oracle 数据库

1、数据完整性规则：域完整性（Domain 栏位列）、实体完整性（Entity 实体行）、参照完整性（Reference）
数据完整性三种方式：a.使用应用代码；b.触发器trigger通过一段代码（过程）满足某个事件行为或动作来验证数据是否满足数据完整性；c.约束条件Constraint作为表结构编码的一部分记录在数据字典中，Oracle会自动维护规则。一般来说，优选使用约束条件来维护数据完整性。

2、约束的类型：NOT NULL 非空、UNIQUE 唯一性、PRIMARY KEY 主键、FOREIGN KEY 外键、CHECK 检查条件
sql>alter table authors add constraint pk_1 primary key(au_id); //主键约束
sql>alter table authors add constraint uk_1 unique(au_lname,au_fname); //唯一性约束
sql>alter table stores add constraint pk_sales primary key(stor_id);
sql>alter table sales add constraint fk_sales foreign key(stor_id) references stores(stor_id);//外键约束
sql>alter table stores add constraint ck_stores_1 check(stor_name like 'B%'); //数据不满足验证条件
sql>input enable novalidate; //新数据生效，表中已经存在的数据不生效。

3、约束状态（4种）
disable novalidate 新数据也不做验证,原来数据也不做验证
disable validate 新数据可以不做验证,原来数据要满足验证
enable novavlidate 新数据做验证，原来数据可以不做验证。
enable validate （默认）新数据做验证，旧数据也做验证。

sql>alter table stores drop constraint ck_stores_1;
sql>alter table stores add constraint ck_stores_1 check(stor_name like 'B%') disable novalidate;
sql>alter table stores modify constraint ck_stores_1 enable;
sql>alter table stores modify constraint ck_stores_1 enable novalidate

sql>alter table sales add constraint ck_sales_1 check(qty>10);
sql>alter table sales add constraint ck_sales_1 check（qty>10) enable novalidate;

sql>update sales set qty=9 where stor_id =6380; //qty=9违反检查约束条件
sql>c /9/11
sql>run //qty=11满足检查约束条件

4、约束检查过程：DML statement -> Check nondefered （当数据通过DML语句改变时，立即进行约束条件检查）->commit执行-> Check deferred constraints 延迟检查约束
sql>alter table sales drop constraint ck_sales_1;
sql>alter table sales add constraint ck_sales_1 check(qty>10) enalbe novalidate;
sql>commit
sql>/
sql>alter table sales add constraint ck_sales_1 check(qty>8) initially deferred;
sql>update sales set qty=5 where stor_id=6380;
sql>commit; //error:事务处理已执行，违反检查约束条件

sql>rollback; //事务已经执行必须rollback回滚。
sql>alter table sales modify constraint ck_sales_1 initially immediate; //DML语句就立即检查验证
sql>update sales set qty=5 where stor_id=6380; //违反检查约束条件。

sql>alter session set constraints=deferred;
sql>update sales set qty=5 where stor_id=6380;// 已经更新2行

sql>alter session set constraints=immediate;
sql>update sales set qty=5 where stor_id=6380; //违反检查约束条件。

5、主键和唯一性约束的状态保持：
键是否可用？如果可用，就看看是否正在使用一个索引（否则不使用索引）？如果是，就判断约束是否可以延迟（否则也判断约束是否可以延迟，如果可以就创建nonunique index，否则就创建unique index）？如果可以，就看看是否索引是non-unique的（否则，使用已存在的索引）？如果否，则使用一个已存在的索引。

6、外键考虑因素：
a.删除Drop主键表，Cascade constraints 清除约束条件
b.清空Truncate主键表，Disable or drop 外键
c.删除Drop包含主键表的表空间，使用CASCADE CONSTRAINTS 清除约束条件
d.在子表执行DML时，确认表空间中包含主键表的键是在线状态的。

sql>drop table stores cascade constraints;
sql>truncate table stores; //外键和主键有关系,无法清空
sql>alter table sales disable constraint fk_sales;
sql>truncate table stores; //再次清空就没问题了。

7、当创建表时定义约束条件：
CREATE TABLE hr.employee(id NUMBER(7) CONSTRAINT employee_id_pk PRIMARY KEY
DEFERRABLE
USING INDEX
STORAGE(INITIAL 100K NEXT 100K)
TABLESPACE indx,
last_name VARCHAR2(25)
CONSTRAINT emplyee_last_name_nn NOT NULL,dept_id NUMBER(7))
TABLESPACE users;

//定义主键可以是可以延迟的，指定索引，索引可以指定存储参数。

8、定义约束的规则：
主键和唯一性约束：主键和唯一性约束的索引放在一个独立的表空间中；如果经常使用快加载即有大量数据载入的，使用nonunique indexes 非唯一性约束。
如何自己关联外键的三种方式：
初始加载以后定义或使用外键、延迟约束检查（数据插入在提交时验证）、允许外键为空

9、如何使约束可用：
(1)ENABLE NOVALIDATE （允许在运行过程中约束条件起作用，不要约束已经存在的表数据）：表不加任何锁、主键和唯一性键必须使用非唯一性索引。
ALTER TABLE hr.departments ENABLE NOVALIDATE CONSTRAINT dept_pk;

sql>alter table sales enable novalidate constraint ck_sales_1;

(2)ENABLE VALIDATE (默认。在运行过程中约束条件必须满足条件，同样也让已经存在的数据进行验证):锁定表、可以使用唯一也可以用非唯一索引、表里的数据必须是有效的。
ALTER TABLE hr.employees ENABLE VALIDATE CONSTRAINT emp_dept_fk;

假定表中加的值不满足条件的话，就是用EXCEPTIONS Table
sql>alter table sales drop constraint ck_sales_1;
sql>alter table sales add constraint ck_sales_1 check(qty>15)
enable validate //违反检查约束条件（因为有一些违背了约束条件）
/

如何查找这些违背了约束条件的行并重新满足约束条件呢？

处理过程：
a.使用utlexcpt.sql 脚本来创建EXCEPTIONS TABLE

b.ALTER TABLE ...EXCEPTIONS .. 接收违反了规则的数据
c.通过使用在EXCEPTIONS的子查询来定位无效数据的行
d.纠正错误（DML)

e.重新执行ALTER TABLE 来使约束条件满足。

sql>start d:\oracle\ora90\rdbms\admin\utlexcpt.sql
sql>alter table sales add constraint ck_sales_1 check(qty>15)
enable validate
exceptions into exceptions; //无法验证-违反检查约束条件
sql>select * from exceptions
/
sql>update sales set qty=16 where rowid in (select row_id from exceptions);
sql>commit
sql>/

sql>alter table sales add constraint ck_sales_1 check(qty>15)
enalbe validate
exceptions into exceptions; //重新执行，使违反检查约束的数据也满足约束条件。

9、获取约束信息：
DBA_CONSTRAINTS\DBA_CONS_COLUMNS
select * from user_contraints; //查找用户加的约束条件
select * from user_cons_columns; //查找主外键的方式