时间戳

首页 > 解决方案 > 无法将 CHECK 约束应用于 MySQL 表

mysql - 无法将 CHECK 约束应用于 MySQL 表

问题描述

我正在尝试为学生实验室场景构建一个小型数据库模式。首先，我将classes表创建为：

CREATE TABLE classes (
    class_id INT NOT NULL,
    class_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (class_id),
    UNIQUE (class_name)
);

然后我创建labs表为：

CREATE TABLE labs (
    lab_id INT NOT NULL,
    lab_name VARCHAR(50) NOT NULL,
    class_id INT NOT NULL,
    PRIMARY KEY (lab_id),
    UNIQUE (lab_name),
    FOREIGN KEY (class_id) REFERENCES classes (class_id)
);

在此之后，我将students表创建为：

CREATE TABLE students (
    student_id INT AUTO_INCREMENT NOT NULL,
    student_name VARCHAR(50) NOT NULL,
    class_id INT NOT NULL,
    PRIMARY KEY (student_id),
    FOREIGN KEY (class_id) REFERENCES classes (class_id)
);

最后我创建了allotments表格来为学生分配实验室：

CREATE TABLE allotments (
    student_id INT  NOT NULL,
    lab_id INT NOT NULL,
    day_of_week CHAR(3) NOT NULL,
    PRIMARY KEY (student_id, lab_id),
    FOREIGN KEY (student_id) REFERENCES students (student_id),
    FOREIGN KEY (lab_id) REFERENCES labs (lab_id)
);

这似乎很好。但我想在表中添加一个CHECK约束，allotments以便学生仅分配到各自班级的实验室，如下所示：

ALTER TABLE allotments
ADD CHECK (student_id IN (SELECT student_id
                      FROM students INNER JOIN labs USING (class_id)
                      WHERE students.class_id = labs.class_id));

但是当我尝试这个时，我收到以下错误消息： Error Code: 3815. An expression of a check constraint 'allotments_chk_1' contains disallowed function. 0.000 sec 请帮助我解决问题。

标签： mysql

解决方案

我遵循@P.Salmon 的想法来使用触发器，并且我找到了一种应用完整性约束的方法，这样用户只能将实验室分配给学生，前提是student_id并且lab_id两者都属于同一班级。

这是要使用的触发器：

DELIMITER $$
CREATE TRIGGER before_allotments_insert
    BEFORE INSERT
    ON allotments FOR EACH ROW
BEGIN
    DECLARE students_class_id, labs_class_id INT;
    DECLARE error_message VARCHAR(100);
    SET error_message = CONCAT('Invalid lab_id for student_id ', NEW.student_id);
    SELECT class_id INTO students_class_id
    FROM students
    WHERE student_id = NEW.student_id;
    SELECT class_id INTO labs_class_id
    FROM labs
    WHERE lab_id = NEW.lab_id;
    IF students_class_id <> labs_class_id THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = error_message;
    END IF;
END $$
DELIMITER ;

当用户尝试为学生插入新分配并验证条目时，此触发器会自动调用。如果student_id和lab_id不属于同一类，则会显示一条错误消息，限制其进入。

推荐阅读