MySQL中DATE作为复合外键的实现问题及最佳实践

问题场景 #

我有一张极简结构的表T：

CREATE TABLE IF NOT EXISTS T (
    A int(11) NOT NULL,
    B date NOT NULL
);

注：A和B后续会添加外键约束，但不在最小示例中体现。

随后创建表U：

CREATE TABLE IF NOT EXISTS U (
    C int(11) NOT NULL,
    D date NOT NULL,
    FOREIGN KEY (C) REFERENCES T(A),
    FOREIGN KEY (D) REFERENCES T(B)
);

执行后出现错误：

#1005 - Can't create table `DBName`.`u` (errno: 150 "Foreign key constraint is incorrectly formed")

我意识到问题出在date字段的约束上，因为T(B)既不是唯一键也不是主键。但我认为T(A)是唯一的，这应该不构成问题。我了解到这是MySQL的特性，且InnoDB有扩展支持该场景，但添加ENGINE=InnoDB后问题仍未解决。

请问这种场景能否实现？若不能，使用(int(11), date)作为复合外键的最佳实践是什么？

环境详情 #

• 服务器：127.0.0.1 via TCP/IP
• 服务器类型：MariaDB
• 服务器版本：10.4.27-MariaDB
• 协议版本：10
• 服务器字符集：UTF-8 Unicode (utf8mb4)
• 数据库客户端版本：libmysql - mysqlnd 8.2.0
• 运行环境：XAMPP + Apache/2.4.64 (Win64)
• 操作工具：PHPMyAdmin SQL控制台（数据库级别）

解决方案 #

1. 原场景能否实现？ #

不能直接实现。无论是MySQL还是MariaDB的InnoDB引擎，外键约束的目标字段必须是主键、唯一键，或者是复合唯一键/主键中的首字段。你单独给D字段加外键指向T(B)，但T(B)既不是主键也不是唯一键，不符合外键约束的基本要求，哪怕T(A)是唯一的也无法规避这个限制——两个独立的外键约束是分别校验的，互不影响。

2. (int, date)复合外键的最佳实践 #

如果你的需求是让U(C,D)关联T(A,B)的组合，应该创建复合外键，而不是两个独立的单字段外键，具体步骤如下：

步骤1：先给表T创建复合主键或复合唯一键

因为外键关联的目标必须是唯一标识的字段组合，所以先修改表T，给(A,B)添加复合主键或复合唯一键：

-- 方案1：设置(A,B)为复合主键（如果A+B是唯一的业务主键）
ALTER TABLE T ADD PRIMARY KEY (A, B);

-- 方案2：如果A本身是主键，可设置(A,B)为复合唯一键
ALTER TABLE T ADD UNIQUE KEY idx_unique_a_b (A, B);

步骤2：创建表U时添加复合外键

此时在表U中，需要将C和D作为组合字段关联T(A,B)，而不是分别创建外键：

CREATE TABLE IF NOT EXISTS U (
    C int(11) NOT NULL,
    D date NOT NULL,
    -- 创建复合外键，关联T的(A,B)组合
    FOREIGN KEY (C, D) REFERENCES T(A, B)
);

补充说明

• 如果你的业务逻辑中，U的C需要单独关联T的A，同时(C,D)需要关联(A,B)，可以同时保留单字段外键和复合外键，但前提是T(A)已经是主键/唯一键，且T(A,B)是复合唯一键：

CREATE TABLE IF NOT EXISTS U (
    C int(11) NOT NULL,
    D date NOT NULL,
    FOREIGN KEY (C) REFERENCES T(A),
    FOREIGN KEY (C, D) REFERENCES T(A, B)
);

这种情况下，既保证了C对应T中存在的A，也保证了(C,D)的组合在T中存在对应的(A,B)。

内容的提问来源于stack exchange，提问作者Jack Sack