mysql - Mysql无法建表

Question

我正在创建一个最伟大电影的数据库，但出现错误:

第 19 行出现错误 1005 (HY000):无法创建表“Greatest_Movies.genre”(错误号:150)

我已经检查过这个帖子: Error Code: 1005. Can't create table '...' (errno: 150)并尝试了很多事情但没有成功。

这就是实际拥有的:

 DROP DATABASE `Greatest_Movies`;
 CREATE DATABASE  `Greatest_Movies`;
 USE `Greatest_Movies` ;

CREATE TABLE IF NOT EXISTS `movie` (
 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 `film` VARCHAR(255) NOT NULL,
 `director` VARCHAR(255) NOT NULL,
 `release_year` VARCHAR(255) NOT NULL,
 `oscars` TINYINT NULL,
 `IMDB_link` VARCHAR(255) NOT NULL,
 `film_page` VARCHAR(255) NOT NULL,
 `country` VARCHAR(255) NOT NULL,
 `genre` TINYINT UNSIGNED NOT NULL,

 PRIMARY KEY (`id`) )
 ENGINE = InnoDB;

 CREATE TABLE IF NOT EXISTS `genre` (
  `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`id`) REFERENCES `movie`(`genre`))
ENGINE = InnoDB;

我缺少什么？问候

Answer 1

这个脚本可以工作并经过测试，试试吧:

DROP DATABASE `Greatest_Movies`;
 CREATE DATABASE  `Greatest_Movies`;
 USE `Greatest_Movies` ;

DROP TABLE IF EXISTS `genre`;
CREATE TABLE `genre` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `movie`;
CREATE TABLE `movie` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `film` varchar(255) NOT NULL,
  `director` varchar(255) NOT NULL,
  `release_year` varchar(255) NOT NULL,
  `oscars` tinyint(4) DEFAULT NULL,
  `IMDB_link` varchar(255) NOT NULL,
  `film_page` varchar(255) NOT NULL,
  `country` varchar(255) NOT NULL,
  `genre` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `genre_fk` (`genre`),
  CONSTRAINT `genre_fk` FOREIGN KEY (`genre`) REFERENCES `genre` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;