ch5 约束与触发器.ppt

Chapter 5:  Integrity and Security Constraints 、 Triggers in SQL Integrity and keys 完整性和键码 Domain Constraints 域约束 Assertions 断言 Triggers 触发器 Security 安全性 Authorization 用户授权 Authorization in 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. 实体完整性约束的规则：如果属性A是一个基本关系的键码属性，那么他的值不能取空值。 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 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. 使用带关键字UNIQUE的索引语句，在为属性建立索引的同时将属性说明为键码。 For example: create unique index stu_no on student(sno); How an SQL system enforce a key constraint? 原则上，每当我们试图改变数据库时必须对约束进行检验。但是必须明确只有更新关系时关系的键码约束才可能发生违背。实际上，关系元组的删除操作不会导致违背，只有插入或修改操作才会。因而只有对关系进行插入或修改时才检验实施键码约束。这对于SQL系统已成为通用惯例。而有效实施键码约束