mysql表格约束

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL,
    email VARCHAR(50) UNIQUE,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

-- 示例：插入重复值
INSERT INTO students (student_id, student_name, email) VALUES (1, 'Alice', 'alice@example.com');

-- 解决方法：确保student_id或email是唯一的
INSERT INTO students (student_id, student_name, email) VALUES (2, 'Bob', 'bob@example.com');

-- 示例：插入NULL值
INSERT INTO students (student_id, student_name, email) VALUES (3, NULL, 'charlie@example.com');

-- 解决方法：确保student_name不为空
INSERT INTO students (student_id, student_name, email) VALUES (3, 'Charlie', 'charlie@example.com');

-- 示例：插入无效的外键值
INSERT INTO students (student_id, student_name, email, department_id) VALUES (4, 'David', 'david@example.com', 99);

-- 解决方法：确保department_id在departments表中存在
INSERT INTO students (student_id, student_name, email, department_id) VALUES (4, 'David', 'david@example.com', 1);