3 完整性语言实验

3.1 实体完整性实验

3.1.1 实验目的

掌握实体完整性的定义和维护方法。

3.1.2 实验内容和要求

定义实体完整性，删除实体完整性。能够写出两种方式定义实体完整性的 SQL 语句：创建表时定义实体完整性、创建表后定义实体完整性。设计 SQL 语句验证完整性约束是否起作用。

3.1.3 实验重点和难点

实验重点：创建表时定义实体完整性。
实验难点：有多个候选码时实体完整性的定义。

3.1.4 实验内容记录

这里参照 employees 数据库创建一个新的数据库 test;
CREATE DATABASE test;

3.1.4.1 创建表时定义实体完整性（列级实体完整性）

创建雇员表。

1
2
3
4
5
6
7
8
CREATE TABLE employees (
  emp_no      INT             NOT NULL PRIMARY KEY,
  birth_date  DATE            NOT NULL,
  first_name  VARCHAR(14)     NOT NULL,
  last_name   VARCHAR(16)     NOT NULL,
  gender      ENUM ('M','F')  NOT NULL,    
  hire_date   DATE            NOT NULL
);
3.1.4.2 创建表时定义实体完整性（表级实体完整性）

创建雇员表。

1
2
3
4
5
6
7
8
9
CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,    
    hire_date   DATE            NOT NULL,
    CONSTRAINT PK_empno PRIMARY KEY(emp_no)
);

注：MYSQL 不支持约束命名。

3.1.4.3 创建表后定义实体完整性

创建雇员表。

1
2
3
4
5
6
7
8
9
10
CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,    
    hire_date   DATE            NOT NULL
);
ALTER TABLE employees
ADD PRIMARY KEY(emp_no);
3.1.4.4 定义实体完整性（多属性主码）

创建职位表。

1
2
3
4
5
6
7
8
CREATE TABLE titles (
    emp_no      INT             NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,title, from_date)
);
3.1.4.5 有多个候选码时定义实体完整性

当存在多个候选码时，只能定义一个主码，其它的候选码定义唯一性约束。
创建部门表。

1
2
3
4
5
6
CREATE TABLE departments (
    dept_no     CHAR(4)         NOT NULL,
    dept_name   VARCHAR(40)     NOT NULL,
    PRIMARY KEY (dept_no),
    UNIQUE  KEY (dept_name)
);
3.1.4.6 删除实体完整性

删除部门表上的实体完整性。

1
2
3
4
ALTER TABLE departments
DROP INDEX dept_name;
ALTER TABLE departments
DROP PRIMARY KEY;

3.1.5 思考

  • 所有列级完整性约束都可以改写为表级完整性约束，而表级完整性约束不一定能改写为列级完整性约束。请举例说明。

例如多属性主码就只能通过表级完整性约束定义。列级完整性约束可以实现缺省约束和非空约束，而表级完整性约束无法实现。表级完整性约束只能实现主键约束、外键约束、唯一性约束、检查约束
表级完整性约束不一定能改写为列级完整性约束，因为有些表级完整性约束涉及到多个列，而列级完整性约束只能应用于单个列。例如，以下语句使用了表级完整性约束：

1
2
3
4
5
6
CREATE TABLE project_assignments (
 project_id int,
 employee_id int,
 join_date date NOT NULL,
 CONSTRAINT pk_assgn PRIMARY KEY (project_id , employee_id)
);

不能改写为以下语句，因为 PRIMARY KEY 约束不能作为列级完整性约束应用于多个列：

1
2
3
4
5
CREATE TABLE project_assignments (
 project_id int PRIMARY KEY,
 employee_id int PRIMARY KEY,
 join_date date NOT NULL
);
  • 什么情况下会违反实体完整性约束，DBMS 将做何种违约处理？请用实验验证。

当更新数据时，新的数据可能违反实体完整性约束。此时 DBMS 会拒绝执行。
举例：插入的数据中，对应的主码为空值时，就会违反实体完整性约束
image.png
dept_no是主码，所以不能为空

3.2 参照完整性实验

3.2.1 实验目的

掌握参照完整性的定义和维护方法。

3.2.2 实验内容和要求

定义参照完整性，定义参照完整性的违约处理，删除参照完整性。写出两种方式定义参照完整性的 SQL 语句：创建表时定义参照完整性、创建表后定义参照完整性。

3.2.3 实验重点和难点

实验重点：创建表时定义参照完整性。
实验难点：参照完整性的违约处理定义。

3.2.4 实验内容记录

3.2.4.1 创建表时定义参照完整性

在已有员工表的情况下，定义工资表。
列级参照完整性定义。

1
2
3
4
5
6
7
CREATE TABLE salaries (
    emp_no      INT             NOT NULL REFERENCES employees (emp_no),
    salary      INT             NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    PRIMARY KEY (emp_no, from_date)
);

注：经测试，MYSQL 定义列级外键约束无效。
表级参照完整性定义。
注：MYSQL 支持外键约束命名。

1
2
3
4
5
6
7
8
CREATE TABLE salaries (
    emp_no      INT             NOT NULL,
    salary      INT             NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    PRIMARY KEY (emp_no, from_date),
    CONSTRAINT FK_empno FOREIGN KEY (emp_no) REFERENCES employees(emp_no)
);
3.2.4.2 创建表后定义参照完整性
1
2
3
4
5
6
7
8
9
10
CREATE TABLE salaries (
    emp_no      INT             NOT NULL,
    salary      INT             NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    PRIMARY KEY (emp_no, from_date)
); 
ALTER TABLE salaries
ADD CONSTRAINT FK_empno
FOREIGN KEY(emp_no) REFERENCES employees(emp_no);
3.2.4.3 定义参照完整性的违约处理

定义两张表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE departments (
  dept_no CHAR(5) NOT NULL,
  dept_name VARCHAR(40) NOT NULL,
  PRIMARY KEY (dept_no)
);
CREATE TABLE emp_dept (
  emp_no CHAR(10) NOT NULL,
  dept_no CHAR(5),
  CONSTRAINT FK_deptno 
  FOREIGN KEY (dept_no) 
  REFERENCES departments(dept_no)
  ON DELETE SET NULL
  ON UPDATE SET NULL,
  PRIMARY KEY(emp_no)
);

注：如果要定义删除后的行为，则必须不能与已有的限制冲突（如不能定义 NOT NULL）。
插入两条数据：

1
2
3
4
INSERT INTO departments 
VALUES('00001', 'Finance');
INSERT INTO emp_dept
VALUES('10000', '00001');

然后删除被参照表中的元组，之后参照表会变成：

1
2
3
4
5
+--------+---------+
| emp_no | dept_no |
+--------+---------+
| 10000  | NULL    |
+--------+---------+
3.2.4.4 删除参照完整性

删除 emp_dept 上的外码。
注：和书中写法稍有不同。
ALTER TABLE emp_dept DROP FOREIGN KEY FK_deptno;

3.2.5 思考

  • 对于自引用表，例如课程表（课程号、课程名、先修课程号、学分）中的先修课程号引用该表的课程号，请完成如下任务：
    （1）写出课程表上的实体完整性和参照完整性。
    （2）在考虑实体完整性约束的条件下，试举出几种录入课程数据的方法。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE TABLE course (
      cno CHAR(4),
      cname VARCHAR(40),
      cpno CHAR(4),
      ccredit SMALLINT
    );
    ALTER TABLE course
    ADD PRIMARY KEY(cno);
    ALTER TABLE course
    ADD CONSTRAINT FK_cpno
    FOREIGN KEY(cpno) REFERENCES course(cno);
    在录入数据时，可以按照引用的顺序录入数据（总是先录入不存在引用或引用已存在的数据），也可以临时移除完整性约束，在录入数据后，再添加完整性约束。
    如果数据本身无法满足约束，则引入约束时会失败。
    比如先加入数据：
    1
    2
    INSERT INTO course
    VALUES('0001','DataStructure','0002',80);
    这一条数据存在依赖问题，此时再引入完整性约束，则会出现无法引入的情况。

3.3 用户自定义完整性实验

3.3.1 实验目的

掌握用户自定义完整性的定义和维护方法。

3.3.2 实验内容和要求

针对具体应用语义，选择 NULL、NOT NULL、DEFAULT、DEFAULT、UNIQUE、CHECK 等，定义属性上的约束条件。

3.3.3 实验重点和难点

实验重点：NULL、NOT NULL、DEFAULT
实验难点：CHECK

3.3.4 实验内容记录

3.3.4.1 定义属性 NULL、NOT NULL 约束

注：默认约束即为 NULL 约束，即可以为 NULL 。
这里不作示例。

3.3.4.2 定义属性 DEFAULT 约束

DEFAULT 约束用于给定属性的默认值，即不提供值的时候自动填充的值。
这里不作示例。

3.3.4.3 定义 UNIQUE 约束

UNIQUE 约束即必须唯一。

3.3.4.4 使用 CHECK 约束条件

CHECK 可以跟表达式，并且可以引用多个属性。
如：

1
2
3
4
5
...
CHECK (from_date <= to_date),
...
CHECK (grade >= 0 AND grade <=100)
...

4 触发器实验

4.1 触发器实验

4.1 实验目的

掌握数据库触发器的设计和使用方法。

4.2 实验内容和要求

定义 BEFORE 触发器和 AFTER 触发器。能够理解不同类型触发器的作用和执行原理，验证触发器的有效性。

4.3 实验重点和难点

实验重点：触发器的定义。
实验难点：利用触发器实现较为复杂的用户自定义完整性。

4.4 实验内容记录

4.4.1 AFTER 触发器

AFTER 触发器可以用来维护一致性。
比如有一张员工总工资表，那么在加入新的工资记录后，需要更新总工资表。
总工资表：

1
2
3
4
5
6
CREATE TABLE emp_salary_total (
  emp_no INT NOT NULL,
  salary_total INT NOT NULL DEFAULT 0,
  CONSTRAINT FK_empno FOREIGN KEY (emp_no)
  REFERENCES employees(emp_no)
);

建立总工资表后，初始化一次数据：

1
2
3
4
INSERT INTO emp_salary_total
SELECT emp_no, SUM(salary)
FROM salaries
GROUP BY emp_no;

在总工资表上定义触发器：

1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER $$
CREATE TRIGGER TRI_salary_total_DELETE
AFTER DELETE ON salaries
FOR EACH ROW
BEGIN
  UPDATE emp_salary_total e
  SET e.salary_total = (
    SELECT SUM(salary)
    FROM salaries s
    WHERE s.emp_no=OLD.emp_no
  )
  WHERE e.emp_no=OLD.emp_no;
END $$

image.png
下面进行验证：
先查询 10001 的总工资：

1
2
3
SELECT * 
FROM emp_salary_total
WHERE emp_no=10001 $$

得到总工资为：

1
2
3
4
5
+--------+--------------+
| emp_no | salary_total |
+--------+--------------+
|  10001 |      344084  |
+--------+--------------+

image.png
从中删除一条数据：
DELETE FROM salaries WHERE salary=84917 AND emp_no=10001 $$
再查询总工资为：

1
2
3
4
5
+--------+--------------+
| emp_no | salary_total |
+--------+--------------+
|  10001 |      259167  |
+--------+--------------+

image.png
符合预期。
在触发器上不应定义过于复杂（耗时）的动作，例如本例中触发器执行了全扫描更新。更好的处理方式是利用被删除的行的信息来增量更新总工资。

1
2
3
4
5
6
7
8
9
DELIMITER $$
CREATE TRIGGER TRI_salary_total_DELETE
AFTER DELETE ON salaries
FOR EACH ROW
BEGIN
  UPDATE emp_salary_total e
  SET e.salary_total = e.salary_total - OLD.salary
  WHERE e.emp_no=OLD.emp_no;
END $$

此时再删除一条数据，
DELETE FROM salaries WHERE salary=85112 AND emp_no=10001 $$
然后查询总工资为

1
2
3
4
5
+--------+--------------+
| emp_no | salary_total |
+--------+--------------+
|  10001 |      174055  |
+--------+--------------+

image.png
同样符合预期。

4.4.2 BEFORE 触发器

BEFORE 触发器可以用来检查数据更新的合法性，可以用来实现比断言更为复杂的检查（断言只能定义一个 CHECK 子句）。
例如，加入一个日期检查，要求 to_date 必须大于 from_date 。
注：MYSQL 抛出异常的写法和书中给出的不同。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DELIMITER $$
CREATE TRIGGER TRI_salaries_INSERT
BEFORE INSERT ON salaries
FOR EACH ROW
BEGIN
  DECLARE
    v_msg VARCHAR(200);
  IF (NEW.from_date >= NEW.to_date) 
  THEN
    BEGIN
      SET v_msg = 'to_date is EARLIER than from_date!';
      SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = v_msg;
    END;
  END IF;
END; $$

然后插入一条一场数据：

1
2
INSERT INTO salaries
VALUES (10001, 15000, '2020-03-01', '2020-01-01');

会得到错误：
ERROR 1644 (HY000): to_date is EARLIER than from_date!
image.png