我的Oracle 9i学习日志(17)-- 维护数据完整性.a-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

我的Oracle 9i学习日志(17)-- 维护数据完整性.a

简介:
数据完整性保证方法:

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的存储参数。也可以使用LOGGINGNOSORT语句。索引可以是本地或全局分区索引。
 
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
                             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,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章