oracle索引和完整性,Oracle学习笔记(11)维护数据完整性

Oracle学习笔记(11)

维护数据完整性

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; //查找主外键的方式