mysql外键使用例子

CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 插入部门数据
INSERT INTO departments (name) VALUES ('研发部');
INSERT INTO departments (name) VALUES ('市场部');

-- 插入员工数据
INSERT INTO employees (name, department_id) VALUES ('张三', 1);
INSERT INTO employees (name, department_id) VALUES ('李四', 1);
INSERT INTO employees (name, department_id) VALUES ('王五', 2);

-- 查询所有员工及其所属部门
SELECT employees.name AS employee_name, departments.name AS department_name
FROM employees
JOIN departments ON employees.department_id = departments.id;

-- 禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;

-- 插入数据
INSERT INTO employees (name, department_id) VALUES ('赵六', 3);

-- 启用外键检查
SET FOREIGN_KEY_CHECKS = 1;

-- 修改外键约束为ON DELETE SET NULL
ALTER TABLE employees
DROP FOREIGN KEY fk_department_id,
ADD CONSTRAINT fk_department_id FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL;