mysql三个表的外键

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- 示例：插入一条不存在的学生ID的选课记录
INSERT INTO enrollments (enrollment_id, student_id, course_id) VALUES (1, 999, 1);

-- 解决方法：确保学生ID存在
INSERT INTO students (student_id, student_name) VALUES (999, 'New Student');
INSERT INTO enrollments (enrollment_id, student_id, course_id) VALUES (1, 999, 1);

-- 示例：删除一个被引用的课程
DELETE FROM courses WHERE course_id = 1;

-- 解决方法：级联删除
ALTER TABLE enrollments ADD CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE;
DELETE FROM courses WHERE course_id = 1;