mysql - 简单的多对多sql关系

Question

我正在尝试构建最简单的 MySQL 数据库，其中包含两个表 - 用户和 friend 的多对多相关表。

这是我最初的 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.

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

Answer 1

您已经给出了约束名称，这是不必要的。您只需删除名称或重命名 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;

键和外键约束的名称可能会发生冲突，这一事实可能看起来令人困惑。事实上，只需将键和约束视为同一事物的不同类型即可。