覆盖索引能否作为外键约束的目标？附多表索引优化诉求

核心需求回顾 #

需要同时实现：

• 保证t1.key列的唯一性
• 支持t2表的(t1_key, b)复合外键关联到t1.(key, b)
• 仅创建一个索引，避免冗余

可行SQL方案 #

1. 创建t1表并建立唯一复合索引

CREATE TABLE t1
(
  id INT,
  key INT, -- 此处不添加UNIQUE约束
  a VARCHAR(50),
  b INT,
  -- 其他列定义...
);

-- 唯一复合索引：key作为第一列保证唯一性；b作为第二列匹配外键需求
CREATE UNIQUE INDEX t1_key_b_idx ON t1(key, b);

2. 创建t2表与外键约束

CREATE TABLE t2
(
  id INT,
  t1_key INT,
  b INT,
  CONSTRAINT t2_key_b_fk 
    FOREIGN KEY (t1_key, b)
    REFERENCES t1(key, b)
);

方案原理 #

• 保证key唯一性：唯一复合索引的第一列是key，只要key值重复，无论b值如何，都会触发唯一约束，完全满足key列的唯一性要求。
• 支持复合外键：该索引的键列组合(key, b)与外键引用的列顺序、内容完全匹配，符合数据库对复合外键的索引要求（被引用列必须存在对应唯一索引）。
• 无冗余索引：仅创建了一个索引，避免了单独给key加唯一约束再额外建(key,b)索引的冗余问题。

为什么INCLUDE方案不可行 #

数据库的外键约束仅识别索引的键列（即索引定义中ON子句后的列），而INCLUDE的列属于索引的附加存储列，不属于索引的键列，因此无法被外键约束用来验证关联关系，导致外键创建失败。

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