1、7 Constraints and Triggers in SQL,A constraint is a expression or statement stored in the database. A constraint is an active(主動(dòng)性) 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(觸發(fā)器) 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(級聯(lián)) 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,定義表中的約束時(shí)，可以用 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； 沒有命名的約束會(huì)由系統(tǒng)自動(dòng)命名。,7.4 Assertions

28、and Triggers,斷言和觸發(fā)器是數(shù)據(jù)庫模式的一部分。 斷言。是 SQL 邏輯表達(dá)式，任何引起斷言條件為假的操作均被拒絕。 觸發(fā)器。是一系列與某個(gè)事件相關(guān)的動(dòng)作，當(dāng)事件發(fā)生時(shí)，動(dòng)作被執(zhí)行。,7.4.1 Assertions ( SQL Server 不支持 ),斷言可用于限定不同表的元組或?qū)傩蚤g應(yīng)滿足的相關(guān)條件。 斷言 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(觸發(fā)器),基于特定事件觸發(fā)的特定的約束檢驗(yàn)。 一個(gè)觸發(fā)器 trigger 是存儲在某個(gè)表中的一個(gè)命名的數(shù)據(jù)庫對象。當(dāng)該表進(jìn)行某種數(shù)據(jù)更新時(shí)，將自動(dòng)觸發(fā)一組SQL 語句的執(zhí)行。 觸發(fā)器基于所謂“事件 Event條件 Condition動(dòng)作Action”規(guī)則，即 ECA 規(guī)則。 Event 事件：對特定表的數(shù)據(jù)更新指令：insert / delete /update Conditio

30、n 條件：當(dāng)事件發(fā)生后，檢查條件是否滿足：若不滿足，則不執(zhí)行動(dòng)作而狀態(tài)轉(zhuǎn)移。若滿足，則執(zhí)行一組動(dòng)作之后狀態(tài)轉(zhuǎn)移。 Action 動(dòng)作：一組 SQL 指令，動(dòng)作也可用于撤銷事件。,7.4.3 Triggers in SQL Server,不同的 DBMS，其觸發(fā)器的定義語句不同。 SQL Server 中觸發(fā)器定義語句的簡單格式為： 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：觸發(fā)器名； table | view：表名或視圖名； AFTER ：執(zhí)行指定的操作后，激活觸發(fā)器。不能在視圖上定義 AFTER 觸發(fā)器； INSTEAD OF ：執(zhí)行觸發(fā)器而不執(zhí)行觸發(fā) SQL 語句，從而替代觸發(fā)語句的操作； DELETE , INSERT , UPDATE ：激活觸發(fā)器的事件； AS：觸發(fā)器要執(zhí)行的操作； IF UPDATE (column)：測試在指定的列上進(jìn)行的 INSERT 或 UPDATE 操作，不能用于 DEL

32、ETE 操作。 deleted 和 inserted 是邏輯表，在結(jié)構(gòu)上類似于定義觸發(fā)器的表，用于保存用戶操作刪除的舊值或插入的新值。,7.4.3 Triggers in SQL Server,實(shí)例： Salesman( empid, idno, name, gender, phone, deptid ) Department( deptid, name, headerid ) 要求：每個(gè)銷售員只屬于某一個(gè)部門；部門經(jīng)理只能由本部門中某個(gè)銷售員擔(dān)任。 僅靠外鍵建立的參照完整性，不能解決的問題： 當(dāng) update department set headerid = ? where deptid

33、= ? 時(shí)，不能保證新 headerid 在 Salesman 中是本部門成員。 當(dāng) update salesman set deptid=? where empid = ? 時(shí)， 若該銷售員是部門經(jīng)理，而要改變其所在部門時(shí)，不能保證 Department 中該部門的 headerid 自動(dòng)置空值 NULL。,7.4.3 Triggers in SQL Server,第一個(gè)問題解決方法。在 department 表中定義觸發(fā)器： 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 執(zhí)行：update department set headerid = A0044 where deptid = 2; 時(shí)， 自動(dòng)執(zhí)行：update salesman set deptid = 2 where empid = A0044 ,7.4.3 Triggers in SQL Server,第二個(gè)問題解決方法。在 salesman 表中定義觸發(fā)器： 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 =