数据库完整性约束实验

概述

数据库完整性是指数据库中数据的精确度、有效性和一致性。为了确保数据库中的数据保持完整性，开发人员必须实现各种完整性约束，例如主键、外键、非空等。本文将以实验的方式探究数据库完整性约束如何应用。

实验环境

本实验使用MySQL数据库管理系统，MySQL Workbench作为图形化界面。

实验目标

1. 创建数据库，命名为“exp_integrity_test”；

2. 创建三个数据表，分别为“student”、“course”、“score”；

3. 定义适当的数据表结构；

4. 应用完整性约束；

5. 插入数据并测试数据表的完整性；

6. 修改数据表并测试完整性约束的影响。

实验步骤

步骤一：创建数据库和数据表

使用以下命令创建名为“exp_integrity_test”的数据库：

```sql

CREATE DATABASE exp_integrity_test;

```

使用以下命令在该数据库中创建三个数据表：

```sql

CREATE TABLE student(

stu_id INT(11) PRIMARY KEY,

stu_name VARCHAR(20) NOT NULL,

stu_age INT(11) NOT NULL

);

CREATE TABLE course(

cou_id INT(11) PRIMARY KEY,

cou_name VARCHAR(20) NOT NULL

);

CREATE TABLE score(

score_id INT(11) PRIMARY KEY,

stu_id INT(11),

cou_id INT(11),

score INT(11),

FOREIGN KEY(stu_id) REFERENCES student(stu_id),

FOREIGN KEY(cou_id) REFERENCES course(cou_id)

);

```

步骤二：定义数据表结构

在创建数据表时，我们定义了三个数据表的结构，具体如下：

- student表包含三个列：stu_id、stu_name、stu_age。其中，stu_id是主键，stu_name和stu_age列不允许为空。

- course表包含两个列：cou_id、cou_name。其中，cou_id是主键，cou_name列不允许为空。

- score表包含四个列：score_id、stu_id、cou_id、score。其中，score_id是主键，stu_id和cou_id列为外键，参照student表和course表中的主键。score列存储成绩。

步骤三：应用完整性约束

我们将应用以下完整性约束，以确保数据的完整性：

- student表的stu_id是主键，stu_name和stu_age列不允许为空；

- course表的cou_id是主键，cou_name列不允许为空；

- score表的score_id是主键，stu_id和cou_id列为外键，参照student表和course表中的主键。

以下脚本用于创建上述完整性约束：

```sql

ALTER TABLE student ADD CONSTRAINT student_primary_key PRIMARY KEY (stu_id);

ALTER TABLE student MODIFY COLUMN stu_name VARCHAR(20) NOT NULL;

ALTER TABLE student MODIFY COLUMN stu_age INT(11) NOT NULL;

ALTER TABLE course ADD CONSTRAINT course_primary_key PRIMARY KEY (cou_id);

ALTER TABLE course MODIFY COLUMN cou_name VARCHAR(20) NOT NULL;

ALTER TABLE score ADD CONSTRAINT score_primary_key PRIMARY KEY (score_id);

ALTER TABLE score ADD CONSTRAINT score_stu_fk FOREIGN KEY (stu_id) REFERENCES student(stu_id);

ALTER TABLE score ADD CONSTRAINT score_cou_fk FOREIGN KEY (cou_id) REFERENCES course(cou_id);

```

步骤四：插入数据及测试

我们将使用以下SQL语句向数据表中插入数据：

```sql

-- 添加学生

INSERT INTO student (stu_id, stu_name, stu_age) VALUES (1, '张三', 20);

INSERT INTO student (stu_id, stu_name, stu_age) VALUES (2, '李四', 21);

INSERT INTO student (stu_id, stu_name, stu_age) VALUES (3, '王五', 19);

INSERT INTO student (stu_id, stu_name, stu_age) VALUES (4, '赵六', 20);

-- 添加课程

INSERT INTO course (cou_id, cou_name) VALUES (1, '数学');

INSERT INTO course (cou_id, cou_name) VALUES (2, '英语');

INSERT INTO course (cou_id, cou_name) VALUES (3, '物理');

-- 添加成绩

INSERT INTO score (score_id, stu_id, cou_id, score) VALUES(1, 1, 1, 80);

INSERT INTO score (score_id, stu_id, cou_id, score) VALUES(2, 1, 3, 90);

INSERT INTO score (score_id, stu_id, cou_id, score) VALUES(3, 2, 1, 70);

```

然后，我们将测试一下上述语句对完整性约束的影响：

- 尝试向student表插入一条stu_id为空的记录，数据库弹出错误信息“Error Code: 1048. Column 'stu_id' cannot be null”。

- 尝试向course表插入一条cou_name为空的记录，数据库弹出错误信息“Error Code: 1364. Field 'cou_name' doesn't have a default value”。

- 尝试向score表插入一条参照不存在的学生记录的成绩，数据库弹出错误信息“Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails”。

步骤五：修改数据及测试

我们将修改stu_id为1的学生的stu_id为2，并测试完整性约束的影响：

尝试执行以下SQL语句：

```sql

UPDATE student SET stu_id = 2 WHERE stu_id = 1;

```

数据库会弹出错误信息“Error Code: 1062. Duplicate entry '2' for key 'PRIMARY'”，这说明主键不能重复。

步骤六：删除约束

我们可以使用以下命令删除约束：

```sql

ALTER TABLE student DROP PRIMARY KEY;

ALTER TABLE student MODIFY COLUMN stu_name VARCHAR(20) NULL;

ALTER TABLE student MODIFY COLUMN stu_age INT(11) NULL;

ALTER TABLE course DROP PRIMARY KEY;

ALTER TABLE course MODIFY COLUMN cou_name VARCHAR(20) NULL;

ALTER TABLE score DROP PRIMARY KEY;

ALTER TABLE score DROP FOREIGN KEY score_stu_fk;

ALTER TABLE score DROP FOREIGN KEY score_cou_fk;

```