SQL语句写在学生表和选课表间建立为维护参照完整性使用的级联删除触发器、级联修改触发器和受限插入触发器

创建学生表：

CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    gender VARCHAR(10) NOT NULL
);

创建选课表：

CREATE TABLE course (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    teacher VARCHAR(50) NOT NULL,
    credit FLOAT NOT NULL
);

创建选课记录表：

CREATE TABLE student_course (
    id INT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    score FLOAT,
    FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (course_id) REFERENCES course(id) ON DELETE CASCADE ON UPDATE CASCADE
);

级联删除触发器：

CREATE TRIGGER delete_student_course
AFTER DELETE ON student
FOR EACH ROW
BEGIN
    DELETE FROM student_course WHERE student_course.student_id = OLD.id;
END;

级联修改触发器：

CREATE TRIGGER update_student_course
AFTER UPDATE ON student
FOR EACH ROW
BEGIN
    UPDATE student_course SET student_id = NEW.id WHERE student_course.student_id = OLD.id;
END;

受限插入触发器：

CREATE TRIGGER insert_student_course
BEFORE INSERT ON student_course
FOR EACH ROW
BEGIN
    DECLARE student_count INT;
    DECLARE course_count INT;
    
    SELECT COUNT(*) INTO student_count FROM student WHERE student.id = NEW.student_id;
    SELECT COUNT(*) INTO course_count FROM course WHERE course.id = NEW.course_id;
    
    IF student_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid student id';
    END IF;
    
    IF course_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid course id';
    END IF;
END;