Can't Create table in Mysql

Question

I'm creating a database of greatest Movies, and i'm getting error:

ERROR 1005 (HY000) at line 19: Can't create table 'Greatest_Movies.genre' (errno: 150)

I have checked this Post: Error Code: 1005. Can't create table '...' (errno: 150) and tried lot of things but with no succes.

This is what actually have:

 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;

What i'm missing? Regards

You would need to add an index on `genre` in `movie` to do that. But I guess that you actually want to create the foreign key from `movie` to `genre` ("every movie should belong to one existing genre"), so add it in that table. — Solarflare, Feb 20 '17 at 19:06
You are right. I want that every movie belongs to a genre, so I changed the foreign key from genre to movie and then change the order of "Create Tables" and it worked properly. Thanks @Solarflare! — Albeis, Feb 20 '17 at 19:26

score 1 · Answer 1 · answered Feb 20 '17 at 19:32

This script work and tested, try it:

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;

Thanks 4Each! I did something similar !:) – Albeis Feb 20 '17 at 19:38 — Albeis, Feb 20 '17 at 19:38

score 0 · Answer 2 · answered Feb 20 '17 at 19:05

0

I think you need to remove the comma from the end of this line:

`genre` TINYINT UNSIGNED NOT NULL,

And also after:

`name` VARCHAR(255) NOT NULL,

answered Feb 20 '17 at 19:05

HomerPlata

1,687
5
22
39

score 0 · Accepted Answer · answered Feb 20 '17 at 19:36

As @Solarflare said on the comments, I did this changes:

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


CREATE TABLE IF NOT EXISTS `genre` (
`id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`))

ENGINE = InnoDB;

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`),
 FOREIGN KEY (`genre`) REFERENCES `genre`(`id`))
ENGINE = InnoDB;

Can't Create table in Mysql

3 Answers3