数据库课程ch6 完整性和安全性.ppt

Chapter 6: Integrity and Security Domain Constraints Referential Integrity Assertions断言 Triggers Security Authorization Authorization in SQL Domain Constraints Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency. Domain constraints are the most elementary form of integrity constraint. They test values inserted in the database, and test queries to ensure that the comparisons make sense. New domains can be created from existing data types E.g. create domain Dollars numeric(12, 2) create domain Pounds numeric(12,2) We cannot assign or compare a value of type Dollars to a value of type Pounds.把一个域中的值转换到另一个域中。 However, we can convert type as below (cast r.A as Pounds)  (Should also multiply by the dollar-to-pound conversion-rate) Domain Constraints (Cont.) The check clause in SQL-92 permits domains to be restricted: Use check clause to ensure that an hourly-wage domain allows only values greater than a specified value. create domain hourly-wage numeric(5,2) constraint value-test 约束名 check(value = 4.00) The domain has a constraint that ensures that the hourly-wage is greater than 4.00 The clause constraint value-test is optional; useful to indicate which constraint an update violated. Can have complex conditions in domain check create domain AccountType char(10) constraint account-type-test check (value in (‘Checking’, ‘Saving’)) 在deposit关系中指定约束： check (branch-name in (select branch-name from branch)) 这个条件必须不仅仅是在deposit中插入或修改一个元组的时候检测，而且在关系branch中的元组被删除或修改时也要检测。 drop domain hourly-wage alter domain hourly-wage drop constraint value-test alter domain hourly-wage add constraint value