MariaDB创建houses表时提示car_id重复键名错误排查

尝试执行以下SQL创建houses表：

CREATE TABLE `houses` (
  `id`         int(11)      NOT NULL AUTO_INCREMENT,
  `room_id`    int(11)      NOT NULL,
  `car_id`     int(11)      NOT NULL UNIQUE,
  `name`       varchar(128) NOT NULL,
  `is_deleted` tinyint(1)            DEFAULT 0,
  `sell_at`    datetime         NULL DEFAULT NULL,
  `created_at` datetime     NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime     NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY   (`id`),
  KEY `room_id` (`room_id`),
  KEY `car_id`  (`car_id`),
  CONSTRAINT `houses_ibfk_1`
    FOREIGN KEY (`room_id`)
    REFERENCES `rooms` (`id`),
  CONSTRAINT `houses_ibfk_2`
    FOREIGN KEY (`car_id`)
    REFERENCES `cars` (`id`)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
);

执行后返回错误：

ERROR 1061 (42000): Duplicate key name 'car_id'

已尝试用以下查询确认约束情况（注：原查询条件不完整，需指定具体库表才能精准排查）：

SELECT (
  table_schema, table_name, constraint_name
) FROM information_schema.table_constraints
WHERE table_schema
ORDER by table_schema, table_name;

疑问：错误是否和car_id的UNIQUE属性有关？去掉该属性后语句可正常执行，但需要确保每个房屋关联的车辆唯一，且创建房屋时必须关联车辆。

错误原因 #

是的，问题直接和car_id的UNIQUE属性相关：
当你给car_id添加UNIQUE约束时，MariaDB会自动创建一个名为car_id的唯一索引；而你又在语句中显式声明了KEY car_id (car_id)，也就是手动创建了一个同名的普通索引。数据库不允许同一表中存在同名的键（索引/约束），因此触发Duplicate key name错误。

解决方案 #

要保留car_id的唯一性要求，同时解决报错，有两种可行方式：

1. 推荐方案：移除手动创建的car_id普通索引
   UNIQUE约束自动生成的唯一索引已经能满足查询需求，无需重复创建普通索引。修改后的SQL如下：

   CREATE TABLE `houses` (
     `id`         int(11)      NOT NULL AUTO_INCREMENT,
     `room_id`    int(11)      NOT NULL,
     `car_id`     int(11)      NOT NULL UNIQUE,
     `name`       varchar(128) NOT NULL,
     `is_deleted` tinyint(1)            DEFAULT 0,
     `sell_at`    datetime         NULL DEFAULT NULL,
     `created_at` datetime     NOT NULL DEFAULT current_timestamp(),
     `updated_at` datetime     NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
     PRIMARY KEY   (`id`),
     KEY `room_id` (`room_id`),
     CONSTRAINT `houses_ibfk_1`
       FOREIGN KEY (`room_id`)
       REFERENCES `rooms` (`id`),
     CONSTRAINT `houses_ibfk_2`
       FOREIGN KEY (`car_id`)
       REFERENCES `cars` (`id`)
         ON DELETE CASCADE
         ON UPDATE NO ACTION
   );
   

   这样既保留了UNIQUE约束确保车辆唯一关联，又避免了键名冲突。

2. 可选方案：修改手动索引的名称
   如果一定要保留手动创建的索引（不推荐，会造成索引冗余），可以给它换个唯一名称，比如idx_car_id：

   KEY `idx_car_id` (`car_id`),
   

需求验证 #

• car_id设置为NOT NULL：确保创建房屋时必须关联车辆；
• UNIQUE约束：确保同一车辆不能被多个房屋关联，完全满足业务要求。

另外，你之前查询约束的语句存在问题，正确的查询应该指定数据库名和表名，才能精准查看houses表的约束：

SELECT table_schema, table_name, constraint_name
FROM information_schema.table_constraints
WHERE table_schema = '你的数据库名称' 
  AND table_name = 'houses'
ORDER BY table_schema, table_name;

内容的提问来源于stack exchange，提问作者Paul-Marie