2

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

0

1 Answer 1

3

you missed s in your table name.

change this

   REFERENCES `group` (`id`)

to

   REFERENCES `groups` (`id`)

DEmo

Sign up to request clarification or add additional context in comments.

1 Comment

1: Why? 2. I tried with on delete restrict (it's more usefull if I cannot remove non-empty groups), but I got same error 3: Even removing ON DELETE and ON UPDATE the problem is still here, cannot create

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.