mysql #1005 -无法创建表“xxx.house_auctions”(errno: 150) (详细信息…)

Question

我不知道为什么会发生这个错误。下面是我使用的表链。我认为这不应该是太困难的事情。会感谢所有我能得到的帮助。

mysql #1005 -无法创建表“xxx.house_auctions”(errno: 150) (详细信息…)

CREATE TABLE IF NOT EXISTS `players` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `group_id` int(11) NOT NULL DEFAULT '1',
  `account_id` int(11) NOT NULL DEFAULT '0',
  `level` int(11) NOT NULL DEFAULT '1',
  `vocation` int(11) NOT NULL DEFAULT '0',
  `health` int(11) NOT NULL DEFAULT '150',
  `healthmax` int(11) NOT NULL DEFAULT '150',
  `experience` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `vocation` (`vocation`)
) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS `houses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner` int(11) NOT NULL,
  `paid` int(10) unsigned NOT NULL DEFAULT '0',
  `warnings` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL,
  `rent` int(11) NOT NULL DEFAULT '0',
  `town_id` int(11) NOT NULL DEFAULT '0',
  `bid` int(11) NOT NULL DEFAULT '0',
  `bid_end` int(11) NOT NULL DEFAULT '0',
  `last_bid` int(11) NOT NULL DEFAULT '0',
  `highest_bidder` int(11) NOT NULL DEFAULT '0',
  `size` int(11) NOT NULL DEFAULT '0',
  `beds` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `owner` (`owner`),
  KEY `town_id` (`town_id`)
) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS `house_auctions` (
  `house_id` INT UNSIGNED NOT NULL,
  `player_id` INT NOT NULL,
  `bid` INT UNSIGNED NOT NULL DEFAULT 0,
  `limit` INT UNSIGNED NOT NULL DEFAULT 0,
  `endtime` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`house_id`,`player_id`),
  FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

Answer 1

我认为house_auctions列的类型与要引用的列不同。试试这个(注意INT(11)而不是INT无签名)：

CREATE TABLE IF NOT EXISTS `house_auctions` (
  `house_id` INT(11)  NOT NULL,
  `player_id` INT(11) NOT NULL,
  `bid` INT UNSIGNED NOT NULL DEFAULT 0,
  `limit` INT UNSIGNED NOT NULL DEFAULT 0,
  `endtime` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`house_id`,`player_id`),
  FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE=INNODB;