上海交通大学：《数据库系统原理 The principle of Database System》课程教学资源（课件讲稿）Chapter7 Constraints and Triggers

Chapter 7 Constraints and Triggers Keys and foreign keys Constraints on attributes and tuples Modification of constraints Assertions triggers

Chapter 7 Constraints and Triggers z Keys and foreign keys z Constraints on attributes and tuples z Modification of constraints z Assertions z triggers

Why use integrity constraints? To catch data-entry errors. o As correctness criteria when writing database updates. To enforce consistency across data in the database. To tell the system about the data it may choose to store the data or process a queries accordingly

Why use integrity constraints? zTo catch data-entry errors. z As correctness criteria when writing database updates. zTo enforce consistency across data in the database. zTo tell the system about the data - it may choose to store the data or process a queries accordingly

Constraints and Triggers A constraint is a relationship among data elements that the DBMS is required to enforce. -Example:key constraints. Triggers are only executed when a specified condition occurs,e.g., insertion of a tuple. Easier to implement than many constraints

Constraints and Triggers zA constraint is a relationship among data elements that the DBMS is required to enforce. – Example: key constraints. zTriggers are only executed when a specified condition occurs, e.g., insertion of a tuple. – Easier to implement than many constraints

Types of Constraints (1)Non-null,unique (2)Key (3)Referential integrity(Foreign-keys) (4)Attribute-based Check (5)Tuple-based Check (6)General assertions global constraints

Types of Constraints (1) Non-null, unique (2) Key (3) Referential integrity (Foreign-keys) (4) Attribute-based Check (5) Tuple-based Check (6) General assertions = global constraints

Constraints with key,not null and unique key constraints:not null,unique. o Not null constraints:not null. .Unique constraints can be null, but unique. Many unique constraints in a table, but only one key constraints. Key constraint forbids null's in the attributes of the key,but unique permits them

Constraints with key, not null and unique zkey constraints: not null, unique. zNot null constraints: not null. zUnique constraints : can be null, but unique. zMany unique constraints in a table, but only one key constraints. zKey constraint forbids null’s in the attributes of the key,but unique permits them

Foreign Keys In relation R a clause that "attribute A references S(B)"says that whatever non- null values appear in the A column of R must also appear in the B column of relation S.B must be declared the primary key for S. Example: CREATE TABLE Beers(name CHAR(20)PRIMARY KEY,manf CHAR(20)); CREATE TABLE Sells bar CHAR(20),beer CHAR(20)REFERENCES Beers(name),price REAL); We expect a beer value is a real beer---something appearing in Beers.name

Foreign Keys In relation R a clause that “attribute A references S(B)” says that whatever non￾null values appear in the A column of R must also appear in the B column of relation S. B must be declared the primary key for S. Example: CREATE TABLE Beers ( name CHAR(20) PRIMARY KEY, manf CHAR(20) ); CREATE TABLE Sells ( bar CHAR (20), beer CHAR(20) REFERENCES Beers(name), price REAL) ; We expect a beer value is a real beer --- something appearing in Beers.name

Expressing Foreign Keys Use the keyword REFERENCES,either: Within the declaration of an attribute,when only one attribute is involved. 2 As an element of the schema,as: FOREIGN KEY () REFERENCES ) Referenced attributes must be declared PRIMARY KEY or UNIQUE

Expressing Foreign Keys z Use the keyword REFERENCES, either: 1. Within the declaration of an attribute, when only one attribute is involved. 2. As an element of the schema, as: FOREIGN KEY ( ) REFERENCES ( ) z Referenced attributes must be declared PRIMARY KEY or UNIQUE

Example:With Attribute CREATE TABLE Beers name CHAR (20) PRIMARY KEY manf CHAR(20)): CREATE TABLE Sells bar CHAR(20) beer CHAR (20) REFERENCES Beers (name), price REAL

Example: With Attribute CREATE TABLE Beers ( name CHAR(20) PRIMARY KEY, manf CHAR(20) ); CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) REFERENCES Beers(name), price REAL );

Example:As Element CREATE TABLE Beers（ name CHAR(20) PRIMARY KEY manf CHAR(20)): CREATE TABLE Se11s（ bar CHAR(20) beer CHAR(20) price REAL FOREIGN KEY (beer) REFERENCES Beers (name));

Example: As Element CREATE TABLE Beers ( name CHAR(20) PRIMARY KEY, manf CHAR(20) ); CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price REAL, FOREIGN KEY(beer) REFERENCES Beers(name));

What happens when a foreign key Constraint is violated Two ways: 1.Insert or update a Sells tuple so it refers to a nonexistent beer always rejected. 2. Delete or update a Beers tuple that has a beer value some Sells tuples refer to a Default:reject b) Cascade:Ripple changes to referring Sells tuple c )Set null

What happens when a foreign key Constraint is violated ? Two ways: 1. Insert or update a Sells tuple so it refers to a nonexistent beer Æ always rejected. 2. Delete or update a Beers tuple that has a beer value some Sells tuples refer to a) Default: reject b) Cascade: Ripple changes to referring Sells tuple c) Set null