4约束与触发器.pptVIP

第四章　约束与触发器 Integrity　constraints　完整性约束 Triggers触发器 Security安全性 Authorization 用户授权 嵌入式SQL 动态SQL Integrity 完整性 Types of Integrity Entity integrity 实体完整性 Referential integrity参照完整性 User－defined integrity用户定义完整性 Entity integrity实体完整性 Key 键码 Keys are attributes or sets of attributes that uniquely identify an entity within its entity set. For example : student(sno,sname,ssex,sage,sdept) key:sno sc(sno,cno,score) key:(sno,cno) Key attribute 键码属性（主属性） In the relation student: sno is a key attribute. In the relation sc: sno is a key attribute and cno is another key attribute. Notes: key attributes == keys Entity integrity实体完整性 The rule of entity integrity is : If attributes A is the key attributes of a basic relation , it’s value cannot be null. For example: in the relation student: sno cannot be null And in the relation sc:sno cannot be null ,also cno cannot be null . Key constraints键码约束in SQL Keys constraints Key constraints means that two tuples of a relation are forbidden to agree on an attribute declared to be a key.or on all of a set attributes that are together declared to form a key. 键码约束是指禁止关系的两个元组在说明为键码的属性上一致，或者禁止在共同构成键码的属性集的所有属性上一致。 For example : student中的sno不能取相同值。 同理:SC 中不能有相同（sno，cno）的组合。即不能出现同一个学生选修同一门课的两个元组。 Key constraints 键码约束 Declaring Keys two ways: Using the key words PRIMARY KEY Using the key words UNIQUE Enforcing实施 key constraints 使用带关键字UNIQUE的索引语句，在为属性建立索引导的同时将属性说明为键码。 Using the index with key word UNIQUE to declare an attribute to be a key at the same time it creates an index on tat attribute. For example: create unique index stu_no on student(sno); How an SQL system enforce a key constraint? 原则上，每当我们试图改变数据库时必须对约束进行检验。但是必须明确只有更新关系时关系的键码约束才可能发生违背。实际上，关系元组的删除操作不会导致违背，只有插入或修改操作才会。因而只有对关系进行插入或修改时才检验实施键码约束。这对于SQL系统已成为通用惯例。而有效实施键码约束，建立索引是非常重要的。 在单个关系上的完整性约束Integrity Constraints on a Single Relation not null primary key unique check (P), where P is a predicate Not－null