如何添加其他数据库的表中列并配置on update cascade属性

前置说明 #

• 仅同实例下的跨库表支持配置外键约束，跨实例场景无法直接实现ON UPDATE CASCADE，需要通过触发器或业务逻辑同步
• 操作账号需要同时持有两张表的ALTER、REFERENCES权限
• 新增的关联列字段属性（类型、长度、字符集、排序规则、是否无符号等）必须和关联目标列完全一致，否则外键创建会失败

操作步骤 #

1. 新增关联列 #

首先在现有表中新增和目标库关联列属性一致的字段，示例（假设需要关联用户库中的用户表的user_id列，列属性为INT UNSIGNED）：

ALTER TABLE Orders
ADD user_id INT UNSIGNED;
-- 如果列不允许为空则调整为：ADD user_id INT UNSIGNED NOT NULL;

2. 同步历史关联数据（可选） #

如果当前表已经存在业务数据，需要先从关联库同步对应列的值，否则添加外键时会触发约束校验失败报错，MySQL同步示例：

UPDATE Orders o
JOIN 用户库.用户表 c ON o.order_user_account = c.user_account
SET o.user_id = c.user_id;

3. 配置跨库外键及ON UPDATE CASCADE #

添加外键约束时直接指定库名.表名(关联列)即可，示例：

ALTER TABLE Orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id)
REFERENCES 用户库.用户表(user_id)
ON UPDATE CASCADE;

不同数据库的跨库表引用格式略有差异：

• MySQL：库名.表名
• SQL Server：库名.架构名.表名，默认架构为dbo时格式为库名.dbo.表名
• PostgreSQL：同实例下跨schema场景用schema名.表名，跨实例需先创建外部表映射后再配置约束

常见异常排查 #

• 报错无法添加外键约束时，优先检查两个关联列的属性是否完全一致，以及关联目标列是否是目标表的主键或有唯一索引
• 报错违反外键约束时，检查当前表新增列的已有值是否都存在于关联目标库的表的对应列中

内容的提问来源于stack exchange，提问作者חיים חדד