CODEWITHSUNDEEP
mysqlsqlindexingmany-to-many
mike_hornbeck
mike_hornbeck

Reputation: 1622

Simple many-to-many sql relation

I'm trying to build the simplest MySQL db with two tables - users and many-to-many related table for friends.

So here's my initial 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;

and now when trying to create the second table :

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;

I'm getting the following error in Inno log :

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

How do I need to set the indexes to make this work ?

Upvotes: 0

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You have given your constraints names, which is unnecessary. You could just remove the names or rename one of the FK_FRIENDS_2 constraints. However, I like the more compact syntax:

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;

The fact that the name of a key and a foreign key constraint might conflict may seem confusing. In fact, just think of keys and constraints as being different types of the same thing.

Upvotes: 2

Related Questions