MySQL如何配置约束实现新增删除users表时自动同步login表数据

你需要配合使用外键级联删除约束和INSERT触发器两个特性实现需求，具体操作如下：

1. 修复login表外键设置，实现删除自动同步 #

你给出的原login建表语句存在语法错误（外键定义放在了表定义括号外），修改时直接给外键增加ON DELETE CASCADE属性即可实现：删除users表用户时，login表关联的对应记录自动删除。

正确的login表建表语句（含级联删除） #

CREATE TABLE login(
  id INT NOT NULL AUTO_INCREMENT, -- 建议设置为自增主键，无需手动维护id
  email VARCHAR NOT NULL,
  password VARCHAR NOT NULL,
  user_id INT NOT NULL,
  PRIMARY KEY(id),
  UNIQUE KEY (user_id), -- 避免同一个用户对应多条登录凭据
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

已存在login表的修改语句（MySQL为例） #

-- 先删除原有外键，注意替换为你实际的外键名
ALTER TABLE login DROP FOREIGN KEY login_ibfk_1;
-- 重新添加带级联删除的外键
ALTER TABLE login ADD FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

2. 创建INSERT触发器实现新增用户自动同步登录凭据 #

在users表上创建AFTER INSERT触发器，每次新增用户后自动把对应字段写入login表，不同数据库的触发器语法略有差异，以下是常见数据库的示例：

MySQL 触发器示例 #

DELIMITER //
CREATE TRIGGER trg_users_after_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO login (email, password, user_id)
  VALUES (NEW.email, NEW.password, NEW.id);
END //
DELIMITER ;

PostgreSQL 触发器示例 #

-- 先创建触发函数
CREATE OR REPLACE FUNCTION sync_user_to_login()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO login (email, password, user_id)
  VALUES (NEW.email, NEW.password, NEW.id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器
CREATE TRIGGER trg_users_after_insert
AFTER INSERT ON users
FOR EACH ROW EXECUTE FUNCTION sync_user_to_login();

注意事项 #

• 如果你的login表id没有设置自增，需要自行在触发器中生成唯一id，优先设置id为自增主键可降低维护成本
• 给login表的user_id加上唯一约束可以避免异常场景下出现重复的用户登录凭据数据

内容的提问来源于stack exchange，提问作者Abdul-razak Twaha