mysql - 简单的多对多SQL关系

我正在尝试用两个表构建最简单的MySQL数据库-用户和朋友的多对多相关表。

所以这是我的初始SQL：

/*
 Source Server Type    : MySQL
 Source Server Version : 50614
 Target Server Type    : MySQL
 Target Server Version : 50614
 File Encoding         : utf-8
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8_polish_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_idx` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

SET FOREIGN_KEY_CHECKS = 1;

现在，当尝试创建第二个表时：

DROP TABLE IF EXISTS `friends`;
CREATE TABLE `friends` (
  `user_id` int(10) unsigned NOT NULL,
  `friend_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`user_id`,`friend_id`),
  KEY `FK_FRIENDS_2` (`friend_id`),
  CONSTRAINT `FK_FRIENDS_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `FK_FRIENDS_2` FOREIGN KEY (`friend_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我在Inno日志中遇到以下错误：

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2014-07-15 02:10:36 1341ab000 Error in foreign key constraint of table pc/friends:
 FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `FK_FRIENDS_2` FOREIGN KEY (`friend_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

我需要如何设置索引才能使其正常工作？

最佳答案

您已经给您的约束名称了，这是不必要的。您可以删除名称或重命名FK_FRIENDS_2约束之一。但是，我喜欢更紧凑的语法：

CREATE TABLE `friends` (
  `user_id` int(10) unsigned NOT NULL REFERENCES `users` (`id`),
  `friend_id` int(10) unsigned NOT NULL REFERENCES `users` (`id`),
  PRIMARY KEY (`user_id`, `friend_id`),
  KEY (`friend_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

键名和外键约束可能会冲突的事实似乎令人困惑。实际上，只需将键和约束视为同一事物的不同类型即可。

关于mysql - 简单的多对多SQL关系，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24748111/