MySQL - cannot add foreign key

Question

I have a table in MySQL InnoDB created like that:

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `type` enum('MANUAL','FACEBOOK') NOT NULL DEFAULT 'MANUAL',
  `role` enum('COOK','HOST','ALL') NOT NULL DEFAULT 'ALL',
  `about_me` varchar(1000) DEFAULT NULL,
  `food_preferences` varchar(1000) DEFAULT NULL,
  `cooking_experience` varchar(1000) DEFAULT NULL,
  `with_friend` bit(1) DEFAULT b'0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

Next I tried adding a table with such a statement (no foreign keys added while creating the table as had problem with that):

CREATE TABLE `messages` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `from` bigint(20) NOT NULL,
  `to` bigint(20) NOT NULL,
  `content` varchar(10000) NOT NULL,
  `timestamp_sent` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `timestamp_read` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Having tables created, I need to finally add foreign keys on 'from' and 'to' fields referencing 'users'.'id' field:

ALTER TABLE `messages` 
  ADD CONSTRAINT `messages_users_fk`
  FOREIGN KEY (`from` , `to` )
  REFERENCES `users` (`id` , `id` )
  ON DELETE SET NULL
  ON UPDATE CASCADE
, ADD INDEX `messages_users_fk_idx` (`from` ASC, `to` ASC)

The error I get is:

ERROR: Error 1822: Failed to add the foreign key constraint. Missing index for constraint 'messages_users_fk' in the referenced table 'users'.

But 'users' table has a PRIMARY index on 'id'...

Also tried to make a smaller step and add foreign key just for 'from' field:

ALTER TABLE `messages` 
  ADD CONSTRAINT `messages_users_fk`
  FOREIGN KEY (`from` )
  REFERENCES `users` (`id` )
  ON DELETE SET NULL
  ON UPDATE CASCADE
, ADD INDEX `messages_users_fk_idx` (`from` ASC) ;

The error is slightly different:

ERROR: Error 1825: Failed to add the foreign key constraint on table 'messages'. Incorrect options in FOREIGN KEY constraint 'cook4food/messages_users_fk'.

The types of the fields are the same (bigint(20) NOT NULL) as it was suggested as the cause of the problem in other StackOverflow threads. My tables are not partitioned (MySQL manual states this as a limitation for having foreign key constraints in InnoDB). The 'messages' table stores no rows currently, so data stored can't be the issue in any manner. I'm stuck, please help.

+1 Lovely to see actual CREATE TABLE statements to play with them :) — Álvaro González
– Álvaro González, Commented Mar 11, 2013 at 12:34

Álvaro González · Accepted Answer · 2013-03-11 12:34:13Z

16

If you have the appropriate privileges, you can issue this query:

SHOW ENGINE innodb STATUS

... which will tell you (among some other info) the exact details of the error:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
130311 13:30:06 Error in foreign key constraint of table test/#sql-71c_6:

  FOREIGN KEY (`from` , `to` )
  REFERENCES `users` (`id` , `id` )
  ON DELETE SET NULL
  ON UPDATE CASCADE
, ADD INDEX `messages_users_fk_idx` (`from` ASC, `to` ASC):
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.

On parent record deletion, you can't make child record NULL because the columns are NOT NULL:

  `from` bigint(20) NOT NULL,
  `to` bigint(20) NOT NULL,

Edit: Additionally, I can't see the purpose of a single composite key. I think you want two single keys instead.

answered Mar 11, 2013 at 12:34

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

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

4 Comments

tomek.g1989 Over a year ago

StackOverflow community helpful and quick as always :-). I can see my mistake now, thank you!

antonagestam Over a year ago

Any ideas if SHOW ENGINE innodb STATUS doesn't show LATEST FOREIGN KEY ERROR?

Salathiel Genese Over a year ago

Thank U ! +1 Alavaro. You save me froma second night ! Thanks again.

Green Over a year ago

Thanks, @Álvaro González. I could find my mistake fast.

Tim · Accepted Answer · 2013-03-11 12:33:47Z

0

REFERENCESusers(id,id)

You are attempting to create an invalid foreign key (id, id). The columns involved in a composite index (one involving more than one column) cannot be the same column, repeated. At least I have never seen that.

Create two separate foreign keys, one for FROM and the other for TO, each one pointing back at users.

answered Mar 11, 2013 at 12:33

Tim

8,99031 gold badges111 silver badges197 bronze badges

1 Comment

tomek.g1989 Over a year ago

Yes, I was thinking about the difference between these two approaches. Separate foreign keys are what I really need. Thanks.

Piyas De · Accepted Answer · 2013-03-11 12:55:25Z

0

As we have seen,

You can not define SET NULL in child records when the child columns from and to are not null.

So if you try to create the foreign keys with the following 2 statements, you should have the foreign keys in messages table.

1>

ALTER TABLE `messages`  ADD CONSTRAINT `messages_users_from_fk`   FOREIGN KEY (`from` )   REFERENCES `users` (`id` )  ON DELETE CASCADE ON UPDATE CASCADE, ADD INDEX `messages_users_fk_idx` (`from` ASC) ;

and

2>

ALTER TABLE `messages`  ADD CONSTRAINT `messages_users_to_fk`   FOREIGN KEY (`to` )   REFERENCES `users` (`id` )  ON DELETE CASCADE ON UPDATE CASCADE, ADD INDEX `messages_users_fk_to_idx` (`to` ASC) ;

Hope these statements help you.

answered Mar 11, 2013 at 12:55

Piyas De

1,76215 silver badges27 bronze badges

Collectives™ on Stack Overflow

MySQL - cannot add foreign key

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related