文档介绍:Chapter 7 Constraints and Triggers
第7章约束与触发器
7 Constraints and Triggers
A constraint is
a relationship among data elements that the DBMS is required to enforce.
a expression or statement stored in the database.
an active element, it execute when a certain event occurs or the database changes.
Example: key constraints.
Triggers are only executed when a specified condition occurs, ., insertion of a tuple.
Easier to implement plex constraints.
7 Constraints and Triggers
SQL2 provides integrity constraints:
key, referential integrity, domain constraints, CHECK constraints, assertions
SQL3 provides trigger which is a form of active element that is called into play on certain specified events.
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 have an unique primary key.
If some attribute is declared to be Unique, then it is a candidate key.
Referential integrity is that values for certain attributes must make sense.
Declaring Keys
How to declare a primary key?
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.
Example:
CREATE TABLE department
(
deptid integer PRIMARY KEY,
name char(40) NOT NULL,
headerid char(10) NULL
);
Declaring 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:
CREATE TABLE salesitem
(
orderno char(10),
lineno char(4),
merid char(6) NOT NULL,
unitprice float NOT NULL,
quantity int NOT NULL,
Primary Key(orderno, lineno)
);
The way of GUI is more convenient.
Keys Declared With UNIQUE
CREATE TABLE Salesman
(
empid char(10) PRIMARY KEY,
IDNo char(18) UNIQUE,
name char(10) NOT NULL,
…
);
Keys Declared With UNIQUE
The distinguish and relationship between Primary K