CODEWITHSUNDEEP
mysqlsqlmariadb
Mo Beigi
Mo Beigi

Reputation: 1765

errno: 150 "Foreign key constraint is incorrectly formed"

I'm getting the 150 error when attempting to run the following setup script. Whats wrong?

serverPermissions seems to trigger the error but I'm not exactly sure why. I think the foreign key constraints are all properly mapped as far as I can tell. I am probably missing something obvious.

-- Setup my members database
DROP DATABASE IF EXISTS mymembers;
CREATE DATABASE IF NOT EXISTS mymembers;

/*
* The list of all members in the community
*/
DROP TABLE IF EXISTS members;
CREATE TABLE members
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30),
steamID3 INT NOT NULL UNIQUE,
PRIMARY KEY (id)
);

/*
* Lists all servers in operation
*/
DROP TABLE IF EXISTS servers;
CREATE TABLE servers
(
sid INT NOT NULL AUTO_INCREMENT,
sname VARCHAR(30), -- name of server (short name)
PRIMARY KEY(sid)
);

/*
* Maps a member and a server together along with a flag. 
* Record (0, 12, 1, 0) indicates member 12 has flag 0 on server 1.
*/
DROP TABLE IF EXISTS serverPermissions;
CREATE TABLE serverPermissions
(
mid INT,
sid INT,
flagid INT,
PRIMARY KEY(mid, sid, flagid),
FOREIGN KEY (mid) REFERENCES members(id),
FOREIGN KEY (sid) REFERENCES servers(sid),
FOREIGN KEY (flagid) REFERENCES flags(id)
);

/*
* flags are used to give custom permissions to players.
* For example a record may be: (0, "VIP", "This play is very important")
*/
DROP TABLE IF EXISTS flags;
CREATE TABLE flags
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(10),
description VARCHAR(100),
PRIMARY KEY(id)
);

Upvotes: 1

Views: 10141

Answers (3)

itsraghz
itsraghz

Reputation: 1017

This link (https://mariadb.org/mariadb-innodb-foreign-key-constraint-errors/) gives a quick snapshot of the engine types that support the foreign keys.

As far as I know, the following storage engines for MariaDB and/or MySQL support foreign keys:

InnoDB (both innodb_plugin and XtraDB)
PBXT (https://mariadb.com/kb/en/mariadb/about-pbxt/)
SolidDB for MySQL (http://sourceforge.net/projects/soliddb/)
ScaleDB (https://mariadb.com/kb/en/mariadb/scaledb/ and http://scaledb.com/pdfs/TechnicalOverview.pdf)
MySQL Cluster NDB 7.3 or later (https://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndb-innodb-engines.html)

Upvotes: 0

Vandan Phadke
Vandan Phadke

Reputation: 71

While adding foreign key constraints, both the tables should have storage type as InnoDb. Change the type for both tables from MyISAM to InnoDB

Upvotes: 2

Rahul
Rahul

Reputation: 77866

Change the order of table creation. You should create flags table before creating serverPermissions. See a Demo Here.

Upvotes: 3

Related Questions