I'm trying to create a schema from a script, so that the different places I work on it can have the current schema definition. Anyway, most of my tables have one or more foreign keys. The first few tables with foreign keys create just fine, the last two picks and scores. This is what I have, unrelated fields removed to minimize.
CREATE TABLE IF NOT EXISTS `teams` (
`id` INT(2) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY `id` (`id`)
) Engine=InnoDB;
CREATE TABLE IF NOT EXISTS `games` (
`id` INT unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY `id` (`id`)
) Engine=InnoDB;
CREATE TABLE IF NOT EXISTS `scores` (
`game_id` INT unsigned NOT NULL,
`team_id` INT(2) unsigned NOT NULL,
FOREIGN KEY (`game_id`) REFERENCES `games`(`id`),
FOREIGN KEY (`team_id`) REFERENCES `teams`(`id`)
) Engine=InnoDB;
CREATE TABLE IF NOT EXISTS `groups` (
`id` INT unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY `id` (`id`)
) Engine=InnoDB;
CREATE TABLE IF NOT EXISTS `ranks` (
`id` INT unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY `id` (`id`)
) Engine=InnoDB;
CREATE TABLE IF NOT EXISTS `group_ranks` (
`id` INT unsigned NOT NULL AUTO_INCREMENT,
`group_id` INT unsigned NOT NULL,
`rank_id` INT unsigned NOT NULL,
PRIMARY KEY `id` (`id`),
FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`),
FOREIGN KEY (`rank_id`) REFERENCES `ranks`(`id`)
) Engine=InnoDB;
-- This Table failed with error code 150
CREATE TABLE IF NOT EXISTS `picks` (
`user_id` INT unsigned NOT NULL,
`game_id` INT unsigned NOT NULL,
`group_id` INT unsigned NOT NULL,
`team_id` INT(2) unsigned NOT NULL,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`),
FOREIGN KEY (`game_id`) REFERENCES `games`(`id`),
FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`),
FOREIGN KEY (`team_id`) REFERENCES `teams`(`id`)
) Engine=InnoDB;
-- This table failed with error code 150
CREATE TABLE IF NOT EXISTS `group_members` (
`group_id` INT unsigned NOT NULL,
`user_id` INT unsigned NOT NULL,
`group_rank_id` INT unsigned NOT NULL,
FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`),
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`),
FOREIGN KEY (`group_rank_id`) REFERENCES `group_ranks`(`id`)
) Engine=InnoDB
My searches showed non -matching column definitions causing error code 150, however I don't see anything different with the any of the pairs not matching, and as you can see other FKs are working its just the last two tables. So my question is: Do you see why these last two create tables are failing? What else could be causing error code 150?
Final note: I've tried changing the order of the last 2 tables, that's how I know they are both failing =D
