Geek asks and answers

MySQL - cannot add foreign key

I have a table in MySQL InnoDB created like this:

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 to add a table with this expression (no foreign keys were added when creating the table, as a problem with this):

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

After creating the tables, I finally need to add the foreign keys to the fields 'from'and 'to'that refer to the field 'users'.'id':

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 foreign key constraint. There is no pointer to the "messages_users_fk" restriction in the "users" table referenced.

But the table 'users' has an index PRIMARYon 'id'...

Also tried to take a smaller step and add a foreign key only for the field 'from':

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:

: 1825: . FOREIGN KEY 'cook4food/messages_users_fk'.

(bigint(20) NOT NULL), StackOverflow. ( MySQL InnoDB). 'messages' , - . , , .

+5
3

, :

SHOW ENGINE innodb STATUS

..., ( ) :

------------------------
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.

NULL, NOT NULL:

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

: , . , .

+14

REFERENCES ( , )

(id, id). , ( ), , . , .

, FROM, TO, .

0

,

You can not specify SET NULL records in the child when the child columns from, and toare not zero.

So, if you try to create foreign keys with the following two operators, you must have foreign keys in the table messages.

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) ;

We hope that these statements will help you.

0
source

More articles:


All Articles