Constraints and Triggers

目录

Kinds of Constraints

Single-Attribute Keys

Multiattribute Key

Foreign Keys

Expressing Foreign Keys

Enforcing Foreign-Key Constraints

Actions Taken

Attribute-Based Checks

Timing of Checks

Tuple-Based Checks

Assertions

Timing of Assertion Checks

Triggers: Motivation

Event-Condition-Action Rules

Preliminary Example: A Trigger

• 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 complex constraints.

Kinds of Constraints

• Keys.

• Foreign-key, or referential-integrity.

• Value-based constraints.

• Constrain values of a particular attribute.

• Tuple-based constraints.

• Relationship among components.

• Assertions: any SQL boolean expression.

Single-Attribute Keys

• Place PRIMARY KEY or UNIQUE after the type in the declaration of the attribute.

CREATE TABLE Beers (name CHAR(20) UNIQUE,manf CHAR(20)
);

Multiattribute Key

The bar and beer together are the key for Sells:
CREATE TABLE Sells (bar CHAR(20),beer VARCHAR(20),price REAL,PRIMARY KEY (bar, beer)
);

多属性键，使用锁哥属性来标志唯一元组，适用于单属性无法标识的场景

Foreign Keys

• Values appearing in attributes of one relation must appear together in certain attributes of another relation.（一个关系中的属性必定会出现在其他关系中）

• Example: in Sells(bar, beer, price), we might expect that a beer value also appears in Beers.name .

Expressing Foreign Keys

Use keyword REFERENCES, either:

• After an attribute (for one-attribute keys).

• As an element of the schema:

FOREIGN KEY (<list of attributes>) REFERENCES <relation> (<attributes>)

Referenced attributes must be declared

PRIMARY KEY or UNIQUE

Enforcing Foreign-Key Constraints

If there is a foreign-key constraint from relation R to relation S, two violations are possible:

• An insert or update to R introduces values not found in S.

• A deletion or update to S causes some tuples of R to “dangle.”

Actions Taken

• Default : Reject the modification.（默认情况是拒绝数据修改操作）

• Cascade : Make the same changes in Sells.（级联检查）

Deleted beer: delete Sells tuple.

Updated beer: change value in Sells.

• Set NULL : Change the beer to NULL.

Attribute-Based Checks

Tuple-Based Checks

CHECK (<condition>) may be added as a relation-schema element.

The condition may refer to any attribute of the relation.

But other attributes or relations require a subquery.

Checked on insert or update only

Triggers: Motivation

• Assertions are powerful, but the DBMS often can’t tell when they need to be checked.

• Attribute- and tuple-based checks are checked at known times, but are not powerful.

• Triggers let the user decide when to check for any condition.

断言虽然功能比较强大但是触发的时机不明确，基于属性和元组的检查虽然触发时机比较明确但是逻辑功能比较有限，而触发器就比较好的解决了这两者的缺陷，将优点结合了起来。

Event-Condition-Action Rules

Another name for “trigger” is ECA rule, or event-condition-action rule.

• Event : typically a type of database modification, e.g., “insert on Sells. ”（一般来说是指数据的修改类型）

• Condition : Any SQL boolean-valued expression.（SQL语句是触发器被触发的条件）

• Action : Any SQL statements.（动作，也就是触发器被触发后需要执行的SQL语句）

Preliminary Example: A Trigger

Instead of using a foreign-key constraint and rejecting insertions into Sells(bar, beer, price) with unknown beers, a trigger can add that beer to Beers, with a NULL manufacturer.

Example: Trigger Definition

CREATE TRIGGER BeerTrig
AFTER INSERT ON Sells
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.beer NOT IN
(SELECT name FROM Beers))
INSERT INTO Beers(name)
VALUES(NewTuple.beer);