数据库Oracle第4章2.ppt

数据库应用技术 第四章 数据库对象 与数据库设计,4.2 约束,4.2.1 约束的作用与分类,在实际业务中，通常会要求数据必须满足一系列条件，这些条件能够保证数据的一致性和完整性，称为商业规则。实现商业规则有三种方式： 应用程序代码：在客户端和程序内部检查，这种方式无法完全保证一致性。 触发器：在DBMS内部，通过程序代码对所有数据的变化都进行检查，这种方式可以保证一致性，但执行代价较大。 约束(Constraints)：对于一些常见、通用的要求，使用设置的方式建立规则。这种方式设置简单、执行代价较小、一致性强。 数据类型：最基本的约束。,ORACLE支持5种类型的完整性约束 NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK,4.2.2 定义与维护约束,NOT NULL 非空约束是字段级约束，要求表中所有行在该字段上都必须有数据值。 CONSTRAINT NULL|NOT NULL,UNIQUE 唯一性约束要求表中任意两行在该字段上的值都不相同，但可以为空。 字段级：CONSTRAINT UNIQUE 可在多个字段上建立一个唯一性约束，表约束： CONSTRAINT UNIQUE () 系统自动建立唯一索引,删除有外键指向的唯一性约束前应：禁用或删除外键。 删除或禁用唯一性约束时：通常同时删除关联的唯一索引，这会降低数据库性能。经常删除或禁用唯一性约束可能导致因丢失索引而带来的性能降低和错误。如何避免？ 1、在唯一性约束保护的数据列上创建非唯一性索引。 2、添加唯一性约束。,PRIMARY KEY 主键约束在表上是唯一的，相当于非空的唯一性约束。可以同时在一个或多个字段上建立。 字段级：CONSTRAINT PRIMARY KEY 表级：CONSTRAINT PRIMARY KEY () 用于表联接。如：学生证号。 唯一性约束和主键约束不会对完全为NULL的键进行处理。,FOREIGN KEY 外键约束作用在父表和子表上 父表上的主键或唯一键 子表外键的值必须：父表字段或者空 在单一字段上建立外键，字段级约束 CONSTRAINT REFERENCES () 在多个字段上建立外键，表级约束 CONSTRAINT FOREIGN KEY () REFERENCES () ON DELETE SET NULL ON DELETE子句：声明父表记录被删除后，对子表记录的处理。缺省情况下禁止在子表记录还存在的情况下删除父表的记录。,使用外键时应该考虑的因素： 外键约束提供了字段参照完整性 外键从句中的字段数目和每个字段指定的数据类型必须和REFERENCES从句中的字段相匹配。 外键不能自动创建，需要用户手动创建。 必须有外键约束参照表的SELECT权限或REFERENCES权限。 一个表中最多可以有31个外键约束。 在临时表中不能使用外键约束。,CHECK 检查约束检查一行记录是否满足条件。 CONSTRAINT CHECK() 列级检查约束只能与限制的字段有关，一个 表级检查约束只能与限制的表中字段有关，多个 检查约束中不能包含子查询 如：工资奖金5000，甲的工资乙的工资2000。,是可选的，如果没提供Oracle将产生一个以SYS_开始的唯一的名字。,举例 CREATE TABLE agents( aid CHAR(3) NOT NULL, lid CHAR(3) NULL, salary NUMBER(4,0) PRIMARY KEY(aid), FOREIGN KEY(lid) REFERNCE locations(lid), CHECK(salary0 AND UPPER(aid)=aid) );,约束存在于数据字典中，在执行SQL或PL/SQL期间使用。 增加约束 NOT NULL使用ALTER的MODIFY子句，其它的使用ALTER的 ADD子句。 CREATE TABLE t(name VARCHAR2(20); ALTER TABLE t MODIFY name NOT NULL; ALTER TABLE t ADD CONSTRAINT t_uk_1 UNIQUE(name); ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY(name); ALTER TABLE t ADD id VARCHAR2(20) CONSTRAINT t_fk_1 REFERENCES tf(id);,修改约束名 ALTER TABLE RENAME CONSTRAINT TO ; 删除约束 ALTER TABLE DROP CONSTRAINT ; 禁止约束 ALTER TABLE DISABLE CONSTRAINT CASCADE; 启用约束 ALTER TABLE ENABLE CONSTRAINT ;,4.2.3 约束的建立原则,1、根据数据库完整性约束的类型确定其实现的系统层次和方式，并提前考虑对系统性能的影响。一般情况下，静态约束应尽量包含在数据库模式中，而动态约束由应用程序实现。 2、实体完整性约束、参照完整性约束是关系数据库最重要的完整性约束，在不影响系统关键性能的前提下需尽量应用。用一定的时间和空间来换取系统的易用性是值得的。 3、要慎用目前主流DBMS都支持的触发器功能，一方面由于触发器的性能开销较大，另一方面，触发器的多级触发不好控制，容易发生错误，非用不可时，最好使用Before型语句级触发器。,4、在需求分析阶段就必须制定完整性约束的命名规范，尽量使用有意义的英文单词、缩写词、表名、列名及下划线等组合，使其易于识别和记忆。如果使用CASE工具，一般有缺省的规则，可在此基础上修改使用。 5、要根据业务规则对数据库完整性进行细致的测试，以尽早排除隐含的完整性约束间的冲突和对性能的影响。 6、要有专职的数据库设计小组，自始至终负责数据库的分析、设计、测试、实施及早期维护。数据库设计人员不仅负责基于DBMS的数据库完整性约束的设计实现，还要负责对应用软件实现的数据库完整性约束进行审核。 7、应采用合适的CASE工具来降低数据库设计各阶段的工作量。好的CASE工具能够支持整个数据库的生命周期，这将使数据库设计人员的工作效率得到很大提高，同时也容易与用户沟通。,练习题,写出图书馆管理系统中建表的DDL语句及相关的约束。,4.3 视图,视图不存储数据， Oracle中将视图以USER_VIEWS中查询语句的形式存储，在使用时组织基表中的数据。 视图的数据可以来自： 1至多个基表、视图。,4.3.1 视图的作用,使用视图的原因 权限控制 简化查询和应用程序 兼容数据库设计的修改,4.3.2 创建、删除与使用视图,视图的创建 CREATE OR REPLACE FORCE|NOFORCE VIEW () AS WITH CHECK OPTIONS CONSTRAINT WITH READ ONLY; OR REPLACE：对已有同名视图进行替换和修改。 FORCE：不管基表是否存在都创建视图。 NOFORCE：只有基表存在时，才创建视图。 ：为的列定义的新名字，没有则用返回的字段名定义。,CREATE OR REPLACE FORCE|NOFORCE VIEW () AS WITH CHECK OPTIONS CONSTRAINT WITH READ ONLY; ：可以包含连接、分组及子查询。 WITH CHECK OPTIONS：对视图插入或修改时，必须满足约束条件。 WITH READ ONLY：视图只读。,举例 CREATE VIEW v1(aid, uname, salary) AS SELECT aid, UPPER(aname), salary FROM agents WHERE salary3000; SELECT * FROM v1; 执行时，系统会找出v1的定义，相当于在基表上执行。,删除视图 DROP VIEW CASCADE|RESTRICT; CASCADE：自动删除依赖该视图的对象，如其它视图。 RESTRICT：如果有依赖对象存在，拒绝删除该视图。 权限：视图的建立者或DROP ANY VIEW权限。 视图的删除不影响基表，不会丢失数据。 举例 DROP VIEW v1;,视图的优点 简单性 简化用户对数据的理解和操作 安全性 通过视图，可以令用户只能查询和修改他们能见到的数据，即限制在数据的不同子集上。 逻辑数据独立性 使应用程序和数据库表在一定程度上独立，即程序-视图-数据库表。,视图的缺点 性能 由多表复杂查询定义的视图 修改限制 由多表复杂查询定义的视图，可能是不可修改的。 定义数据库对象时，应该权衡视图的优点和缺点，合理地定义视图。,4.3.3 视图上的DML操作,视图的分类,复杂视图上的DML操作的准则 不允许违反约束的DML操作。 不能将一个值添加到包含算术表达式的列中。 在非key-preserved表上不允许DML操作。 在包括聚组函数、GROUP BY子句、ROWNUM伪列或DISTINCT关键字的视图上不允许DML操作。,举例 UPDATE v1 SET salary = 1000 WHERE aid = A01; SELECT * FROM v1; 此时，A01不存在了，salary 3000, 对表的操作将及时反映在视图中。,CREATE VIEW v1(aid, uname,salary) AS SELECT aid, UPPER(aname), salary FROM agents WHERE salary3000;,举例 CREATE VIEW v2(cname, ordno, dollars) AS SELECT ame, o.ordno, c.dollars FROM customers c, orders o WHERE c.cid = o.cid; UPDATE v2 SET dollars = 100 WHERE cname =TOM;WHERE ordno = 1012; SET cname = AA WHERE ordno = 1012;,在此，orders是键保留表，可以修改。,在此，customers表不是键保留表，不可以修改。,举例 CREATE VIEW v3 AS SELECT cid, UPPER(aname) uname FROM customers; UPDATE v3 SET uname = A WHERE cid = C01;,不能对uname对应