数据完整性保证方法：
图
01
•
应用程序代码
•
数据库触发器
•
声明完整性约束
具体使用上述哪种方法映射业务规则是设计时应考虑的问题。而数据库管理员主要关心的是实施设计人员选择的方法，并在完整性需求和性能要求之间取得平衡。
应用程序代码既可作为数据库中的存储过程实现，也可作为在客户端上运行的应用程序实现。
数据库触发器是
PL/SQL
程序，在表上发生事件（如插入或更新列）时执行。可以启用或禁用触发器，即可以设置触发器在事件发生时执行，或者将触发器设置为不执行（即使已定义）。通常情况下，创建数据库触发器只是为了强制应用不能定义为完整性约束的复杂业务规则。
完整性约束是执行业务规则的首选机制，这是因为它可以：
•
改善性能
•
易于声明和修改，不需要进行大量编码
•
集中管理规则
•
使用灵活（启用或禁用）
•
在数据字典中完全文档化
约束类型：
图
2
NOT NULL:
•
在列级别定义。
创建表时定义：
SQL> create table dingdan(
2 order_num number(4) constraint nn_order_num not null,
3 order_date date not null,
4 product_id number(3));
表已创建。
SQL> desc dingdan
名称
是否为空
?
类型
----------------------------------------- -------- ----------------------------
ORDER_NUM NOT NULL NUMBER(4)
ORDER_DATE NOT NULL DATE
PRODUCT_ID NUMBER(3)
创建后修改：
SQL> alter table dingdan modify order_date null;
表已更改。
SQL> desc dingdan
名称
是否为空? 类型
----------------------------------------- -------- ----------------------------
ORDER_NUM NOT NULL NUMBER(4)
ORDER_DATE DATE
PRODUCT_ID NUMBER(3)
SQL> alter table dingdan modify product_id not null;
表已更改。
SQL> desc dingdan;
名称
是否为空
?
类型
----------------------------------------- -------- ----------------------------
ORDER_NUM NOT NULL NUMBER(4)
ORDER_DATE DATE
PRODUCT_ID NOT NULL NUMBER(3)
CHECK:
•
可以在列或表级别定义。
•check
表达式中可以引用同一行的其他列值作为参考条件值，但不能做查询。
•Environment function such as SYSDATE,USER,USERENV,UID,and pseudo-columns such as ROWNUM,CURRVAL,NEXTVAL, or LEVEL cannot be used to evaluate the check condition.
•
一列可以定义多个
check
约束。
SQL> create table bonus(
2 emp_id varchar2(40) not null,
3 salary number(9,2),
4 bonus number(9,2),
5 constraint ck_bonus check(bonus>0));
SQL> alter table bonus add constraint ck_bonus2 check (bonus<salary and bonus>=0);
唯一性约束：
•
在列级别只能约束一列，组合键的唯一性约束只能在表级别定义。
•
在创建唯一性约束以后，
Oracle
自动创建一个与被指定的列的唯一性约束索引。如果原来这列定义了索引则使用原来的索引，如果索引时非唯一性的那么必须不能包含相同的键值。在插入操作时先扫描索引。不能显式地
drop
索引，除非先
drop
相应的约束。其他管理方式与普通索引一样。
•
唯一性约束允许列中有多个
null
值，因为
null
值不存储在索引中，所以当插入
null
值时因在索引中找不到而被允许插入。
•
在唯一性约束中允许
null
值存在。
•
在创建唯一性约束键值时，隐式生成的索引可以指定其存储参数，如果没有指定则使用
tablespace
的存储参数。也可以使用
LOGGING
和
NOSORT
语句。索引可以是本地或全局分区索引。
SQL> desc bonus;
名称
是否为空? 类型
----------------------------------------- -------- ----------------------------
EMP_ID NOT NULL VARCHAR2(40)
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)
DEPT VARCHAR2(10)
SQL> alter table bonus add constraint uq_emp_id unique (dept,emp_id)
2 using index tablespace indx
3 storage(initial 32k next 32k pctincrease 0);
表已更改。
SQL> insert into bonus values(2,1200,500,'sale');
已创建
1
行。
SQL> commit;
提交完成。
SQL> select * from bonus;
EMP_ID SALARY BONUS DEPT
------------------------------ ---------- ---------- --------------------
1 1000 500 sale
2 1200 500 sale
SQL> insert into bonus values(1,1200,500,'sale');
insert into bonus values(1,1200,500,'sale')
*
ERROR
位于第
1
行
:
ORA-00001: unique constraint (LUO.UQ_EMP_ID) violated
SQL> insert into bonus values(1,1200,500,'telch');
已创建
1
行。
SQL> select * from bonus;
EMP_ID SALARY BONUS DEPT
------------------------------ ---------- ---------- --------------------
1 1200 500 telch
1 1000 500 sale
2 1200 500 sale
SQL> rollback;
回退已完成。
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------------
BONUS TABLE
CUSTOMERS TABLE
DINGDAN TABLE
ORDERS TABLE
T TABLE
T_ID_IDX INDEX
UQ_EMP_ID INDEX
已选择
7
行。
SQL> drop index uq_emp_id;
drop index uq_emp_id
*
ERROR
位于第
1
行
:
ORA-02429: cannot drop index used for enforcement of unique/primary key
#
如果要
drop
索引必须先
drop
相应的约束。
SQL> create index bonus_id_idx on bonus(emp_id);
索引已创建。
SQL> create index bonus_id_idx on bonus(emp_id,dept);
create index bonus_id_idx on bonus(emp_id,dept)
*
ERROR
位于第
1
行
:
ORA-00955: name is already used by an existing object
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------------
BONUS TABLE
BONUS_ID_IDX INDEX
CUSTOMERS TABLE
DINGDAN TABLE
ORDERS TABLE
T TABLE
T_ID_IDX INDEX
UQ_EMP_ID INDEX
已选择
8
行。
SQL> drop index bonus_id_idx;
索引已丢弃。
主键约束：
•
特性：
Unique+not null
。
•
一个表只能有一个主键。
•
创建一个
Unique+not null
的索引，其他特性与
unique
约束特性相同。
SQL> create table t(id number primary key,name varchar2(10));
表已创建。
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------------
BONUS TABLE
CUSTOMERS TABLE
DINGDAN TABLE
ORDERS TABLE
SYS_C002767 INDEX
T TABLE
UQ_EMP_ID INDEX
已选择
7
行。
SQL> create table tt(id number,pid number,name varchar2(10),
2 constraint pk_id primary key(id,pid));
表已创建。
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------------
BONUS TABLE
CUSTOMERS TABLE
DINGDAN TABLE
ORDERS TABLE
PK_ID INDEX
SYS_C002767 INDEX
T TABLE
TT TABLE
UQ_EMP_ID INDEX
已选择
9
行。
外键约束：
图
3
•
在表级别可以定义多个键值，父表中的列要是主键列或唯一约束列。
•
外键和被引用键所在列可以是同一张表里的不同列即父表与子表是同一个表，如图
4
。
•
外键允许为
null
。
图
4
当父表中被引用的列中某行被删除时子表的相应动作：
Delete no action
：缺省，父表中被引用的列的行值将不允许被删除或修改。
Delete cascade
：父表中某行被删除后，子表中相应的所有项也会被删除。
Delete set null
：父表中某行被删除后，子表中相应的所有项被设置为
null
。
举例：
Alter table city add constraint fk_state foreign key (country_code,state_code) on delete cascade;
Alter table city add constraint fk_state foreign key (country_code,state_code) on delete set null;
实验：
SQL> alter table orders add constraint fk_cust foreign key(cust_code)
2 references customers(cust_code) on delete cascade;
references customers(cust_code) on delete cascade
*
ERROR
位于第
2
行
:
ORA-02270: no matching unique or primary key for this column-list
SQL> alter table customers add constraint pk_cust primary key(cust_code);
表已更改。
SQL> alter table orders add constraint fk_cust foreign key (cust_code)
2 references customers(cust_code)
3 on delete cascade;
表已更改。
SQL> select * from customers;
CUST_C NAME REGION
------ -------------------------------------------------- ----------
A01 TKB SPORT SHOP West
A02 VOLLYRITE North
A03 JUST TENNIS North
A04 EVERY MOUNTAIN South
A05 SHAPE UP South
A06 SHAPE UP West
A07 WOMENS SPORTS South
A08 NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER East
已选择
8
行。
SQL> select * from orders;
ORD_ID ORD_DATE CUST_C DATE_OF_DE PRODUCT_ID
---------- ---------- ------ ---------- --------------------
610 11-11月-97 A01
611 15-11月-97 A02
612 19-11月-97 A04
601 05-3月 -97 A06
602 09-4月 -97 A02
600 05-3月 -97 A03
604 19-4月 -97 A06
605 18-5月 -97 A06
607 22-5月 -97 A04
608 29-5月 -97 A04
603 09-4月 -97 A02
613 06-12月-97 A08
614 06-12月-97 A02
616 08-12
月
-97 A03
619 27-12月-97 A04
617 10-12月-97 A05
615 06-12月-97 A07
618 20-12月-97 A02
已选择
18
行
。
SQL> delete from customers where cust_code='A03';
已删除
1
行。
SQL> select * from orders;
ORD_ID ORD_DATE CUST_C DATE_OF_DE PRODUCT_ID
---------- ---------- ------ ---------- --------------------
610 11-11月-97 A01
611 15-11月-97 A02
612 19-11月-97 A04
601 05-3月 -97 A06
602 09-4月 -97 A02
604 19-4月 -97 A06
605 18-5月 -97 A06
607 22-5月 -97 A04
608 29-5月 -97 A04
603 09-4月 -97 A02
613 06-12月-97 A08
614 06-12月-97 A02
619 27-12月-97 A04
617 10-12月-97 A05
615 06-12月-97 A07
618 20-12月-97 A02
已选择
16
行。
本文转自 d185740815 51CTO博客，原文链接：http://blog.51cto.com/luotaoyang/293801，如需转载请自行联系原作者
