Mysql错误：1215无法添加外键约束

Referenced table is 'group' (InnoDB).

引用表是'group'(InnoDB)。

It has a 'id' column defined as INT(11), not nullable, auto increment, primary key

它有一个'id'列定义为INT(11),不可为空,自动增量,主键

Referencing table is 'user (InnoDB)

引用表是'用户(InnoDB)

It has 'group_id' column defined as INT(11), not nullable.

它将'group_id'列定义为INT(11),不可为空。

In referencing table already is present an unique index based on 'group_id' column

在引用表中,已经存在基于'group_id'列的唯一索引

But whn executing

但是谁在执行

ALTER TABLE `user`
   ADD CONSTRAINT `user_group` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE

I got an error

我收到了一个错误

error: 1215 Cannot add foreign key constraint

错误:1215无法添加外键约束

I add the db dump

我添加了数据库转储

CREATE TABLE IF NOT EXISTS `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `language` varchar(255) NOT NULL,
  `order` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Check List

Is Db InnoDB? Yes

是Db InnoDB吗?是

Are all tables InnoDB ? Yes

所有表都是InnoDB吗?是

Is unique index present on referencing table ? Yes

引用表上是否存在唯一索引?是

Are referenced and referencing column exactly of the same type ? Yes

被引用和引用列完全相同的类型?是

Question is simple: why cannot I create this foreign key ?

问题很简单:为什么我不能创建这个外键?

UPDATE 1: I tried replacing ON DELETE CASCADE with ON DELETE RESTRICT and nothing changes, also I tried to remove ON DELETE and ON UPDATE an nothing changes

更新1:我尝试用ON DELETE RESTRICT替换ON DELETE CASCADE并且没有任何变化,我也尝试删除ON DELETE和ON UPDATE没有任何变化

1 个解决方案

#1

you missed s in your table name.

你错过了表名中的s。

change this

   REFERENCES `group` (`id`)

   REFERENCES `groups` (`id`)

DEmo

#1