samedi 27 juin 2015

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

Aucun commentaire:

Enregistrer un commentaire