Mysql error: 1215 Cannot add foreign key constraint

Question

Referenced table is 'group' (InnoDB).

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

Referencing table is 'user (InnoDB)

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

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

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

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

1. Is Db InnoDB? Yes
2. Are all tables InnoDB ? Yes
3. Is unique index present on referencing table ? Yes
4. 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

Answer 1

you missed s in your table name.

change this

   REFERENCES `group` (`id`)

to

   REFERENCES `groups` (`id`)

DEmo