CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100),
age INT
);
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100),
teacher_name VARCHAR(100)
);
记录学生与教师的关联，enrollments表通过外键student_id和course_id与students表和courses表关联。这意味着每条enrollments表中的记录必须引用students表中存在的student_id和courses表中存在的course_id。这保证了数据的引用完整性。
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)
);
INSERT INTO students (student_id, name, age) VALUES (1, 'Alice', 20);
INSERT INTO students (student_id, name, age) VALUES (2, 'Bob', 22);
INSERT INTO courses (course_id, course_name, teacher_name) VALUES (101, 'Mathematics', 'Mr. Smith');
INSERT INTO courses (course_id, course_name, teacher_name) VALUES (102, 'Physics', 'Mr. Johnson');
INSERT INTO enrollments (enrollment_id, student_id, course_id) VALUES (1, 1, 101); -- Alice 注册 Mathematics 课程
INSERT INTO enrollments (enrollment_id, student_id, course_id) VALUES (2, 1, 102); -- Alice 注册 Physics 课程
INSERT INTO enrollments (enrollment_id, student_id, course_id) VALUES (3, 2, 101); -- Bob 注册 Mathematics 课程
数据库中建立了外键关系，确保了数据的引用完整性。如果尝试插入一个不存在的student_id或course_id到enrollments表中，数据库将拒绝该操作，从而保护了数据的完整性。