10.4.24-MariaDB下person_addresses表外键约束创建失败求助

错误原因分析 #

报错的核心原因是person_addresses表中定义的联合唯一索引uc_person_address (person_id,address)无效，进而导致表中不存在以person_id为首列的有效索引，不符合InnoDB外键约束的要求：

• InnoDB不允许在未指定前缀长度的TEXT类型列上创建唯一索引，你的语句中address是TEXT类型且未指定长度，导致该联合索引创建失败。
• 外键约束要求子表的外键列（此处为person_id）必须存在一个以该列作为首列的索引（单独索引或联合索引前缀），由于上述联合索引失效，系统找不到符合要求的索引，因此触发报错。

解决方案 #

针对这个问题，有两种可行的修改方式：

方式一：给TEXT列指定前缀长度，修复联合唯一索引 #

将person_addresses表中的联合唯一索引修改为指定address列的前缀长度，既能保留person_id+address的唯一约束，又能让索引生效，同时满足外键对索引的要求：

CREATE TABLE person_addresses (
  person_id int(11) NOT NULL ,
  address text NOT NULL,
  created_at timestamp NOT NULL DEFAULT current_timestamp(),
  UNIQUE KEY uc_person_address (person_id, address(255)), -- 指定address的前缀长度
  CONSTRAINT fk_person FOREIGN KEY (person_id) REFERENCES persons(person_id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

注：前缀长度可根据业务需求调整（比如100、255等），只要能区分不同地址即可。

方式二：单独为person_id创建索引（若不需要联合唯一约束） #

如果业务上不需要person_id+address的唯一约束，可直接给person_id单独创建索引，同时去掉无效的联合唯一索引：

CREATE TABLE person_addresses (
  person_id int(11) NOT NULL ,
  address text NOT NULL,
  created_at timestamp NOT NULL DEFAULT current_timestamp(),
  INDEX idx_person_id (person_id), -- 单独创建person_id的索引
  CONSTRAINT fk_person FOREIGN KEY (person_id) REFERENCES persons(person_id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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