A first course in database systems(7).ppt

1、7 Constraints and Triggers in SQL,A constraint is a expression or statement stored in the database. A constraint is an active(主动性) element, it execute when a certain event occurs or the database changes. SQL2 provides integrity constraints: key, referential integrity, domain constraints, check const

2、raints, assertions(断言) SQL3 provides trigger(触发器) which is a form of active element that is called into play on certain specified events.,7.1 Keys and Foreign Keys,Key is the most important kind of constraint. Each table may have several sets of attributes called candidate keys(候选键). Each table can

3、have an unique primary key. If some attribute is declared to be Unique, then it is a candidate key.,7.1.1 Declaring Primary Keys,There are two way to declare a primary key in SQL statement. 1. We may declare an attribute to be a primary key when that attribute is listed in the relation schema. Examp

4、le: CREATE TABLE department ( deptid integer PRIMARY KEY, name char(40) NOT NULL, headerid char(10) NULL );,7.1.1 Declaring Primary Keys,2. We may add to the list of items in the schema an additional declaration that says a particular attribute or set of attributes forms the primary key. Example: CR

5、EATE TABLE salesitem ( orderno char(10), lineno char(4), prodid char(6) NOT NULL, unitprice float NOT NULL, quantity int NOT NULL, Primary Key (orderno, lineno) );,7.1.2 Keys Declared With Unique,The distinguish and relationship between Primary Key and Unique. A table has and only has a primary key,

6、 while it may has any number of Unique declarations. An Unique declaration can be a set of attributes, and an Unique attribute can be null. If a primary key has only one attribute, then the attribute is unique. If a primary key has more than one attribute, then none of the attributes is unique.,7.1.

7、3 Enforcing Key Constraints,Which operations may result key constraint violations? Delete? Insert? Update? SQL system check a key constraint only when an insertion or update to a relation occurs.,7.1.4 Declaring Foreign-Key Constraints,Referential integrity is that values for certain attributes must

8、 make sense. We may declare an attribute or attributes of one relation to be a foreign key, referencing some attribute(s) of a second relation. Pay attention to the distinction between referenced attribute(s) and referencing attribute(s).,7.1.4 Declaring Foreign-Key Constraints,There are two ways to

9、 declare a foreign key. Follow the foreign keys name and type by REFERENCES () Follow the list of items by FOREIGN KEY REFERENCES () Where The referenced attributes should be primary key or unique attribute of the referenced table.,7.1.4 Declaring Foreign-Key Constraints,Example: CREATE TABLE depart

10、ment ( deptid integer PRIMARY KEY, name char(40) NOT NULL, headerid char(10) REFERENCES salesman(empid) ); is equivalent to CREATE TABLE department ( deptid integer PRIMARY KEY, name char(40) NOT NULL, headerid char(10), FOREIGN KEY headerid REFERENCES salesman(empid) );,7.1.4 Declaring Foreign-Key

11、Constraints,Example: CREATE TABLE Elective( Sno CHAR(10) NOT NULL, Cno CHAR(6) NOT NULL, Score INT, PRIMARY KEY (Sno, Cno), FOREIGN KEY Sno References Student(Sno)， FOREIGN KEY Cno References Course(Cno) );,7.1.4 Declaring Foreign-Key Constraints,Can we declare a table to reference itself? Yes. Exam

12、ple: salesman(empid, idno, name, managerid, depid, .) Can we declare a foreign key to have NULL values? Yes.,7.1.5 Maintaining Referential Integrity,How to maintain referential integrity in the face of modifications to the database? There are three alternatives policy(策略): Take example for Salesorde

13、r(orderno, custid, .) referencing Customer(custid, .). the Restrict(限制) policy(default policy): For Salesorder(referencing table), the following actions will be rejected. Insert a new Salesorder tuple whose custid value is not NULL and is not the custid component of any Customer tuple. Update a Sale

14、sorder tuple to change the custid component to a non-NULL value that is not the custid component of any Customer tuple. For Customer(referenced table), the following actions will be rejected. Delete a Customer tuple, and its custid component appears as the custid component of one or more Salesman tu

15、ples. Update a Customer tuple in a way that changes the custid value, and the old custid is the value of custid of some order form.,7.1.5 Maintaining Referential Integrity,the cascade(级联) policy When we delete a Customer(referenced table) tuple, then the referencing tuple(s) is deleted from Salesord

16、er(referencing table). When we update a Customer tuple in a way that changes the custid value, then the custid component of referencing tuple(s) in Salesorder is updated by system.,7.1.5 Maintaining Referential Integrity,the set-NULL(置空) policy Take example for Department(deptid, ., headerid) refere

17、ncing Salesman(empid, .). Firstly, headerid can be set NULL. When we delete a Salesman(referenced table) tuple, then the headerid component of referencing tuples are set null in Department(refrencing table). When we update a Salesman tuple in a way that changes the empid value, then the headerid com

18、ponent of referencing tuples are set null in Department(refrencing table).,7.1.5 Maintaining Referential Integrity,Grammar: references (list of attributes) Action Action: ON Update | Delete Restrict | Cascade | Set Null Note that Update and Delete can be declared different policies for a foreign key

19、. Example: CREATE TABLE department ( deptid integer PRIMARY KEY, name char(40) NOT NULL, headerid char(10) , FOREIGN KEY headerid References salesman(empid) ON DELETE SET NULL ON UPDATE CASCADE );,7.2 Constraints on Attributes and Tuples,Limit the values that may appear in components for some attrib

20、ute. Main ideas Not-null constraints Attribute-Based CHECK Constraints Tuple-Based CHECK Constraints,7.2.1 Not-Null Constraints,How to declare an attribute is NOT NULL? The constraint is declared by the keywords NOT NULL following the declaration of the attribute in a CREATE TABLE statement. The def

21、ault is NULL. The effect of NOT NULL. we can not update the value to be NULL. when we insert a tuple, we must give a nonempty value for the attribute. we can not use the set-null policy. Note that primary key is NOT NULL.,7.2.2 Attribute-Based CHECK Constraints,How to restrict the value of an attrib

22、ute to be in a limited range? Declare CHECK(condition) following the declaration of the attribute. The grammar of condition is same as that of the condition in WHERE clauses. Example: Make any component of custid more than 0 in customer(custid, .). custid INT CHECK(custid0) Example: Make any compone

23、nt of gender either 0(female) or 1(male) in salesman(empid, ., gender, .). gender INT CHECK(gender IN (0,1) An attribute-based CHECK constraint is checked whenever any tuple gets a new value for this attribute. If the constraint is violated by the new value, then the modification is rejected.,7.2.2

24、Attribute-Based CHECK Constraints,When an attribute-based CHECK constraint is checked? An update or a insert is executed for the table. Note that a CHECK condition may include attributes of other relations. Example: Make any deptid value of salesman(empid, ., deptid) be a value of primary key of dep

25、artment. deptid INT CHECK(deptid IN (SELECT deptid FROM department),7.2.3 Tuple-Based CHECK Constraints,Example: For each tuple, require audit(审核) date not earlier than sign date in salesorder(orderno, signdate, auditdate, .). Add the following constraint: CHECK (auditdate = signdate) When check? In

26、sert or update. Note that if there are both attribute-base CHECK constraints and tuple-based CHECK constraints in a relation, then check attribute-base CHECK constraints firstly.,7.2 Constraints on Attributes and Tuples,Exercises P332 7.2.2,7.3 Modification of Constraints,定义表中的约束时，可以用 Constraint 短语给

27、约束命名。 例如： custid INT Constraint CKcustid Check( custid 0 ) 或 orderno INT Constraint PKorderno PRIMARY KEY 可以使用 Alter Table 指令增加或删除约束。 例如： Alter Table salesorder ADD Constraint CKauditdate Check( auditdate = signdate )； 或 Alter Table customer DROP Constraint CKcustid； 没有命名的约束会由系统自动命名。,7.4 Assertions

28、and Triggers,断言和触发器是数据库模式的一部分。 断言。是 SQL 逻辑表达式，任何引起断言条件为假的操作均被拒绝。 触发器。是一系列与某个事件相关的动作，当事件发生时，动作被执行。,7.4.1 Assertions ( SQL Server 不支持 ),断言可用于限定不同表的元组或属性间应满足的相关条件。 断言 Assertion 的建立： CREATE ASSERTION CHECK() 例如：要求 salesman(empid, , deptid) 表的deptid 属性值必须是 department 表的键值。 CREATE ASSERTION ASdeptid CHECK

29、 ( NOT EXISTS ( Select * From salesman where deptid NOT IN ( Select deptid From department );,7.4.2 Triggers(触发器),基于特定事件触发的特定的约束检验。 一个触发器 trigger 是存储在某个表中的一个命名的数据库对象。当该表进行某种数据更新时，将自动触发一组SQL 语句的执行。 触发器基于所谓“事件 Event条件 Condition动作Action”规则，即 ECA 规则。 Event 事件：对特定表的数据更新指令：insert / delete /update Conditio

30、n 条件：当事件发生后，检查条件是否满足：若不满足，则不执行动作而状态转移。若满足，则执行一组动作之后状态转移。 Action 动作：一组 SQL 指令，动作也可用于撤销事件。,7.4.3 Triggers in SQL Server,不同的 DBMS，其触发器的定义语句不同。 SQL Server 中触发器定义语句的简单格式为： CREATE TRIGGER trigger_name ON table | view FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS IF UPDATE ( column ) AND | OR UPDATE

31、 ( column ) .n sql_statement .n ,7.4.3 Triggers in SQL Server,其中： trigger_name：触发器名； table | view：表名或视图名； AFTER ：执行指定的操作后，激活触发器。不能在视图上定义 AFTER 触发器； INSTEAD OF ：执行触发器而不执行触发 SQL 语句，从而替代触发语句的操作； DELETE , INSERT , UPDATE ：激活触发器的事件； AS：触发器要执行的操作； IF UPDATE (column)：测试在指定的列上进行的 INSERT 或 UPDATE 操作，不能用于 DEL

32、ETE 操作。 deleted 和 inserted 是逻辑表，在结构上类似于定义触发器的表，用于保存用户操作删除的旧值或插入的新值。,7.4.3 Triggers in SQL Server,实例： Salesman( empid, idno, name, gender, phone, deptid ) Department( deptid, name, headerid ) 要求：每个销售员只属于某一个部门；部门经理只能由本部门中某个销售员担任。 仅靠外键建立的参照完整性，不能解决的问题： 当 update department set headerid = ? where deptid

33、= ? 时，不能保证新 headerid 在 Salesman 中是本部门成员。 当 update salesman set deptid=? where empid = ? 时， 若该销售员是部门经理，而要改变其所在部门时，不能保证 Department 中该部门的 headerid 自动置空值 NULL。,7.4.3 Triggers in SQL Server,第一个问题解决方法。在 department 表中定义触发器： CREATE TRIGGER update_headerid ON department FOR UPDATE AS IF UPDATE(headerid) Begi

34、n update s set s.deptid = i.deptid from salesman s, inserted i where s.empid = i.headerid End 执行：update department set headerid = A0044 where deptid = 2; 时， 自动执行：update salesman set deptid = 2 where empid = A0044 ,7.4.3 Triggers in SQL Server,第二个问题解决方法。在 salesman 表中定义触发器： CREATE TRIGGER update_deptid ON salesman AFTER UPDATE AS IF ( UPDATE(deptid) AND Exists ( select * from department dep, deleted del where dep.headerid = del.empid ) ) begin update dep set dep.headerid = null from department dep, deleted del where dep.deptid =