CODEWITHSUNDEEP
mysqlkeyaddition
tomek.g1989
tomek.g1989

Reputation: 51

MySQL - cannot add foreign key

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.

Upvotes: 5

Views: 19463

Answers (3)

Piyas De
Piyas De

Reputation: 1764

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.

Upvotes: 0

Álvaro González
Álvaro González

Reputation: 146660

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.

Upvotes: 16

Tim
Tim

Reputation: 8919

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.

Upvotes: 0

Related Questions