MySQL建表报错：不将被引用字段设为主键如何创建外键

报错核心原因 #

• 你编写的两段建表SQL本身存在多处语法错误，会直接导致执行失败
• MySQL InnoDB引擎对外键约束有强制要求：被引用的字段必须是对应表中某个索引的最左列，且字段的类型、字符集、排序规则必须和引用字段完全一致。你当前customers_card表使用(id,cid)作为复合主键，cid是复合索引的第二列，不满足“索引最左列”的要求，因此无法直接作为外键的被引用目标。

前置修正（原有SQL语法错误） #

你写的两段SQL都存在基础语法问题，需要先修正：

1. customers_card建表语句的问题：
   • 约束定义直接写ADD PRIMARY KEY属于ALTER TABLE的语法，不能直接写在CREATE TABLE的字段括号内
   • uid的外键定义漏了字段右括号：FOREIGN KEY (\uid` REFERENCES应为FOREIGN KEY (`uid`) REFERENCES`
   • 后续外键引用时表名误写为customers__card（多了一个下划线），和实际表名customers_card不一致
2. card_info建表语句的问题：
   • 同样存在表名拼写错误，引用的customers__card不存在
   • cid字段未指定和customers_card.cid一致的排序规则，会触发字段类型不匹配错误

解决方案（无需将cid设为单独主键） #

不需要修改customers_card表现有复合主键结构，只需要给cid字段创建一个独立的普通索引，即可满足InnoDB外键的索引要求，操作步骤如下：

1. 若customers_card表尚未创建，使用修正后的建表语句，其中新增cid的普通索引：

CREATE TABLE `customers_card` (
 `id` int NOT NULL,
 `uid` int NOT NULL,
 `cid` varchar(255) COLLATE utf8mb4_hungarian_ci NOT NULL,
 `cardname` varchar(255) COLLATE utf8mb4_hungarian_ci NOT NULL,
 `cardnum` int NOT NULL,
 `expiry` date NOT NULL,
 `cvc` int NOT NULL,
 `value` int NOT NULL,
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`,`cid`),
 -- 给cid加独立普通索引，无需修改原有主键
 KEY `idx_customers_card_cid` (`cid`),
 CONSTRAINT `customers_card_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_hungarian_ci;

如果customers_card表已经存在，直接执行以下语句加索引即可：

ALTER TABLE `customers_card` ADD INDEX `idx_customers_card_cid` (`cid`);

2. 执行修正后的card_info建表语句：

CREATE TABLE `card_info` (
   `id` INT(11) PRIMARY KEY AUTO_INCREMENT,
   `uid` INT(11) NOT NULL,
   -- cid字段的字符集、排序规则和被引用字段完全一致
   `cid` VARCHAR(255) COLLATE utf8mb4_hungarian_ci NOT NULL,
   `number` BIGINT NOT NULL,
   `holder` VARCHAR(255) NOT NULL,
   `type` VARCHAR(255) NOT NULL,
   `provider` VARCHAR(255) NOT NULL,
   CONSTRAINT `card_info_ibfk_uid` FOREIGN KEY (`uid`)
      REFERENCES `customers` (`id`)
      ON DELETE CASCADE,
   CONSTRAINT `card_info_ibfk_cid` FOREIGN KEY (`cid`)
      REFERENCES `customers_card` (`cid`)
      ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_hungarian_ci;

说明 #

普通索引不会改变表现有主键结构，也不会新增唯一性约束，完全符合你“不将cid设为单独主键”的要求，加完索引后外键即可正常创建。

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