MySQL-无法添加外键 [英] MySQL - cannot add foreign key

问题描述

我在MySQL InnoDB中有一个这样创建的表:

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

创建表后，我最终需要在引用'users'.'id'字段的'from'和'to'字段上添加外键:

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)

我得到的错误是:

错误:错误1822:无法添加外键约束.引用表"users"中约束"messages_users_fk"的缺少索引.

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

但是'users'表在'id' ...上有一个PRIMARY索引...

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

还尝试缩小步骤并仅为'from'字段添加外键:

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

错误略有不同:

错误:错误1825:无法在表'messages'上添加外键约束.外键约束'cook4food/messages_users_fk'中的选项不正确.

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

字段的类型与其他StackOverflow线程中提出的问题的原因相同(bigint(20) NOT NULL).我的表未分区(MySQL手册指出这是对InnoDB中具有外键约束的限制). 'messages'表当前不存储任何行，因此以任何方式都不会成为存储数据的问题.我被卡住了，请帮忙.

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.

推荐答案

如果您具有适当的特权，则可以发出以下查询:

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.

在删除父记录时，您不能使子记录为NULL，因为列为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,

编辑:此外，我看不到单个组合键的用途.我想您要改用两个单键.

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

这篇关于MySQL-无法添加外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！