约束与外键

罗大富 BigRich大约 9 分钟MySQL数据库

我们在语法中一直都能看到约束的字段，在 MySQL 中，约束是用于定义和保护数据完整性的规则，它们可以应用于表的列（字段）或整个表。约束类型用于确保数据库中的数据满足特定的条件和限制，从而保持数据的一致性和有效性。以下是 MySQL 中常见的约束类型：

  1. 非空约束（NOT NULL）：非空约束确保表中的某个列不允许包含 NULL 值，该列必须始终有值，比如学生的名字就不能为空，示例：
# 添加 NOT NULL
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;

CREATE TABLE students (
    id INT,
    name VARCHAR(50) NOT NULL,
    age INT
);
  1. 主键约束（PRIMARY KEY）：主键约束用于唯一标识表中的每一行数据。一个表只能有一个主键，并且主键的值不能为 NULL，且必须唯一。比如，我们之前的练习中建立了一个 students 的数据表，其中有一个字段为 id，学生的 ID 是不能重复的，具有唯一性，而且也可以用来作为学生的标识，因此，我们可以给学生的 ID 添加主键约束，示例：
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

由于主键具有唯一性，所以我们可以让主键约束与 AUTO_INCREMENT 自增属性一起使用，来确保在插入新数据的时候，该列的值会自动底层，并保持唯一性。

  1. 唯一约束（UNIQUE）：唯一约束确保表中的某个列的值是唯一的，允许有 NULL 值，但不允许重复的非 NULL 值。

主键约束与唯一约束的区别

同一个表可以有多个唯一约束，允许列值为空，唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值

  1. 自增长约束（AUTO_INCREMENT）:某个字段的值自增，自增约束的列的数据类型必须是整数类型，通常与主键一同使用，示例：
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT
);
  1. 默认值约束（DEFAULT）：给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值，示例
# 分数默认值为 0
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    score INT DEFAULT 0,
);

这些约束类型可以单独使用，也可以组合使用，以满足具体的数据完整性需求。在数据库设计中，适当使用约束可以确保数据的正确性和一致性，并有效地保护数据库免受数据错误和异常的影响。

外键

在 MySQL 中，外键（Foreign Key）也是约束的一种，用来在两个表的数据之间建立连接。外键主要的目的是控制存储在外键表中的数据，保持数据的一致性与完整性。

定义一个外键时，需要遵守下列规则：

  1. 父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
  2. 必须为父表定义主键。
  3. 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
  4. 外键中列的数据类型必须和父表主键中对应列的数据类型相同。

创建外键的语法有两种，一种是在创建表的时候创建外键：

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    teacher_id INT,
    FOREIGN KEY (外键字段名) REFERENCES 外表表名(主键字段名) 
        [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] 
        [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
);

另一种在已有的表中增加外键：

# 添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名)
REFERENCES 外表表名(主键字段名)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

其中，ON DELETE 和 ON UPDATE 表示事件触发限制，各参数意义如下：

参数说明
RESTRICT限制外表中的外键改动（默认值，也是最安全的设置）
CASCADE跟随外键改动，在父表上 update/delete 记录时，同步子表的匹配记录
SET NULL设为 NULL，在父表上 update/delete 记录时，将子表上匹配记录的列设为 NULL，但是要注意子表的外键列不能为 NOT NULL
NO ACTION无动作
SET DEFAULT设为默认值，父表有变更时，子表将外键列设置成一个默认的值

在以老师和学生之间的关系为例，我们可以创建两个表：teachers 表和 students 表。teachers 表保存老师的信息，students 表保存学生的信息。每个学生只有一个班主任老师，这里我们可以使用外键来建立学生表与老师表之间的关联关系。

首先，我们创建 teachers 表：

CREATE TABLE teachers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    subject VARCHAR(50)
);

然后，我们创建 students 表，并在 students 表中添加一个外键列 teacher_id，用于关联学生表与老师表：

ALTER TABLE students ADD CONSTRAINT t_id FOREIGN KEY (teacher_id) REFERENCES teachers(id)

在上面的示例中，我们在 students 表中定义了一个外键 teacher_id，并将其与 teachers 表中的 teacher_id 列建立关联。这样，当在 students 表中插入新的学生记录时，teacher_id 必须是 teachers 表中已存在的 teacher_id 值，否则将会违反外键约束而导致插入失败。

通过这样的设置，我们可以确保学生表中的 teacher_id 值只能是老师表中已存在的值，从而保证了学生和老师之间的正确关联关系。

总结来说，外键是 MySQL 中用于建立表之间关联关系的一种约束。它可以维护数据的完整性和一致性，并在数据库设计中发挥重要作用。在使用外键时，需要注意选择合适的列作为外键，确保数据的正确性和关联关系的准确性。

以下是几个练习示例，涉及插入数据和使用外键约束的操作：

向 teachers 表和 students 表插入数据：

INSERT INTO teachers (name, subject)
VALUES ('老张', '数学'),
       ('老王', '英语'),
       ('老贾', '语文');

INSERT INTO students (name, score, teacher_id)
VALUES ('小王', 77, 1),
       ('小李', 88, 1),
       ('小陈', 99, 2),
       ('小鹏', 87, 3),
       ('小宇', 68, 2);

更新学生信息，更新学生 李四 的班主任为 老贾（id 3）：

UPDATE students SET teacher_id = 3 WHERE name = '李四';

删除一名老师及其关联的学生，删除 teachers 表中 teacher_id 为 3 的老师及其关联的学生：

DELETE FROM teachers WHERE teacher_id = 3;

外键除了可以表示一对多关系外，还可以表示 一对一、多对多，这是表与表之间的三种关系，一对多 最常用，一对一 相对使用较少，比如学生老师的身份证与名字一一对应。多对多 可以拿学生与课程举例，一个学生可以选修多门课程，一个课程可以被多名学生选修。想要表示 多对多 的关系，需要借助第三张表

我们可以先创建 courses 表：

CREATE TABLE couses(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50));

然后，创建 students_courses_relation 表，用于存储两个表的外键：

CREATE TABLE students_courses_relation(
    id INT PRIMARY KEY AUTO_INCREMENT,
    course_id INT,
    student_id INT,
    FOREIGN KEY (course_id) REFERENCES courses(id),
    FOREIGN KEY (student_id) REFERENCES students(id));

接着，我们先向 courses 表插入数据，然后向 students_courses_relation 表插入数据表示学生选择的课程：

INSERT INTO courses(name) VALUES ('语文'), ('数学'), ('英语');

INSERT INTO students_courses_relation(course_id, student_id) VALUES (1, 1),(1, 2) ,(1, 3),(1, 4),(1, 5);

最后，我们该如何在 多对多 关系表中查询相对应的记录呢？比如，我想要查询选修课程 id 为 1 的学生有哪些，或者 XX 学生选修了哪些课程。

JOIN 多表查询

在多表关系中，要查询记录，通常需要使用 JOIN 子句来连接相关的表，并使用条件来指定关联关系。这样可以获取跨多个表的相关数据。在 MySQL 中，常见的多表查询类型有三种：内连接（INNER JOIN）、左连接（LEFT JOIN）和 右连接（RIGHT JOIN）。

以下是每种连接类型的查询示例：

  1. 内连接（INNER JOIN）：内连接返回两个表中匹配的行，即满足连接条件的行。只有当两个表中的条件相匹配时，相关的记录才会被返回。

示例：查询选修了课程的学生及其课程信息。

SELECT students.name, courses.name
FROM students
INNER JOIN students_courses_relation ON students.id = students_courses_relation.student_id
INNER JOIN courses ON students_courses_relation.course_id = courses.id;
  1. 左连接（LEFT JOIN）：左连接返回左表的所有行，以及右表中满足连接条件的行。如果右表中没有与左表匹配的行，则返回 NULL 值。

示例：查询所有学生以及他们选修的课程，包括没有选修课程的学生。

SELECT students.name, courses.name
FROM students
LEFT JOIN students_courses_relation ON students.id = students_courses_relation.student_id
LEFT JOIN courses ON students_courses_relation.course_id = courses.id;
  1. 右连接（RIGHT JOIN）：右连接返回右表的所有行，以及左表中满足连接条件的行。如果左表中没有与右表匹配的行，则返回 NULL 值。

示例：查询所有课程以及选修该课程的学生，包括没有学生选修的课程。

SELECT students.name, courses.name
FROM students
RIGHT JOIN students_courses_relation ON students.id = students_courses_relation.student_id
RIGHT JOIN courses ON students_courses_relation.course_id = courses.id;

这些示例只是展示了多表查询的一部分，实际上，多表查询的灵活性非常高，可以根据具体需求来设计连接和查询条件。请根据你的数据结构和查询需求，选择合适的连接类型和条件来执行查询操作。

上次编辑于:
贡献者: Luo