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.

• Example:

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

• Constraints on the value of a particular attribute.
• Add CHECK(<condition>) to the declaration for the attribute.
• The condition may use the name of the attribute, but any other relation or attribute name must be in a subquery.（Checks允许使用多属性，但是如果出现跨表的多属性，其他表中的属性必须使用子查询）

Timing of Checks

Attribute-based checks are performed only when a value for that attribute is inserted or updated.（基于属性的检查只有在数据插入和更新时才触发）

• Example: CHECK (price <= 5.00) checks every new price and rejects the modification (for that tuple) if the price is more than $5.

• Example: CHECK (beer IN (SELECT name FROM Beers)) not checked if a beer is deleted from Beers (unlike foreign-keys).（这里可以看出与外键约束的不同，在Beers表中数据被删除时并不会检查这个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

对比维度	基于属性的检查（列级约束）	基于元组的检查（表级约束）
能否使用本表中的其他属性？	❌ 不能直接引用。 约束表达式必须仅依赖当前列的值。	✅ 可以直接引用。 可在约束条件中使用同一行的其他列（如 CHECK (end_date > start_date)）。
能否使用跨表属性？	✅ 可以间接使用（需通过子查询）。	✅ 可以直接使用（通过子查询）。
如何使用跨表属性？	1. 子查询必须独立于当前行： 子查询不能引用当前表的其他列，只能依赖固定条件或其他表的数据。 示例： sql<br> CHECK (category_id IN (SELECT category_id FROM Categories))<br>	1. 子查询可结合当前行的属性： 子查询可通过 NEW.column（如 MySQL）或直接引用当前行的列。 示例： sql<br> CHECK (salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = NEW.dept_id))<br>
何时触发 CHECK 检查？	仅在该列的值被插入或更新时触发。 - 若其他列更新，即使整行数据变化，该约束也不会触发。 - 示例：CHECK (price > 0) 仅在 price 列被修改时验证。	仅在整行数据被插入或更新时触发。 - 无论哪一列变化，只要行数据发生修改，约束都会验证。 - 示例：CHECK (end_date > start_date) 在插入或更新任意列时均会验证。

Assertions

• These are database-schema elements, like relations or views.（是一种数据库模式元素）

• Defined by:

CREATE ASSERTION <name>

CHECK (<condition>);

• Condition may refer to any relation or attribute in the database schema.（约束条件可以调用数据模式中的所有属性和关系）

Timing of Assertion Checks

• In principle, we must check every assertion after every modification to any relation of the database.
• A clever system can observe that only certain changes could cause a given assertion to be violated.

Example: No change to Beers can affect FewBar. Neither can an insertion to Drinkers.

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);

Options: FOR EACH ROW

Triggers are either “row-level” or “statement-level. ”

• Row level triggers : execute once for each modified tuple.（行级触发就是变化了几行数据就执行几次触发器）

• Statement-level triggers : execute once for a SQL statement, regardless of how many tuples are modified.（语句级触发就是一行语句不管多少行数据发生变化，都只执行一次触发器）

Options: REFERENCING

• INSERT statements imply a new tuple (for row-level) or new table (for statement-level). The “table” is the set of inserted tuples.

• DELETE implies an old tuple or table.

• UPDATE implies both.

Refer to these by [NEW OLD][TUPLE TABLE] AS <name>

维度	行级触发器（FOR EACH ROW）	语句级触发器（FOR EACH STATEMENT）
触发频率	每插入 / 更新 / 删除 一行数据 触发一次	每执行 一条 SQL 语句 触发一次（无论影响多少行）
数据范围	单次触发处理 单行数据	单次触发处理 全表数据（语句影响的所有行）
数据形态	以 行（元组） 为单位	以 临时表（多行集合） 为单位

Options: The Condition

• Any boolean-valued condition.

• Evaluated on the database as it would exist before or after the triggering event, depending on whether BEFORE or AFTER is used.

But always before the changes take effect.（不管是after还是before，所有数据库的状态都没有真正的发生变化）

Access the new/old tuple/table through the names in the REFERENCING clause