0

I keep getting the "ERROR 1005 (HY000): Can't create table 'jfunchio.rental' (errno: 150)" error where i run this code. I was wondering if anyone could help with what is wrong. I would really appreciate it. My code is below.

create table rental
(  item_rental_id               varchar(8),
   customer_id                  varchar(8),
   movie_id                     varchar(8),
   game_id                      varchar(8),
   rental_status_code           varchar(10),
   rental_date_out              varchar(20),
   rental_date_returned         varchar(20),
   rental_amount_due            numeric(2,2),
   primary key (item_rental_id),
   foreign key (customer_id) references customer (customer_id)
        on delete set null,
   foreign key (movie_id) references movie (movie_id)
        on delete set null,
   foreign key (game_id) references game (game_id)
        on delete set null

)  ENGINE = InnoDB;

create table customer
(  customer_id                  varchar(8),
   first_name                   varchar(20) not null,
   last_name                    varchar(20) not null,
   phone                        varchar(20),
   email                        varchar(50),
   primary key (customer_id)
) ENGINE = InnoDB;

create table movie
(  movie_id             varchar(8),
   movie_genre          varchar(20),
   release_year         numeric(4,0),
   movie_title          varchar(50),
   movie_stock          numeric(2,1),
   movie_rental_rate    numeric(2,2),
   primary key (movie_id)
) ENGINE = InnoDB;

create table game
(  game_id              varchar(8),
   game_genre           varchar(20),
   release_year         numeric(4,0)
   game_title           varchar(50),
   game_stock           numeric(2,1),
   game_rental_rate     numeric(2,2),
   primary key (game_id)
) ENGINE = InnoDB;

create table payment
(  payment_id           varchar(8),
   customer_id          varchar(8),
   paymentMethod_id     varchar(20),
   paymentAmount        numeric(2,2),
   primary key (payment_id),
   foreign key (customer_id) references customer (customer_id)
        on delete set null
) ENGINE = InnoDB;

create table cast
(  movie_id             varchar(8),
   actor_id             varchar(8),
   primary key (movie_id)
) ENGINE = InnoDB;

create table actor
(  actor_id             varchar(8),
   actor_first_name     varchar(20) not null,
   actor_last_name      varchar(20),
   primary key (actor_id)
) ENGINE = InnoDB;

create table rental_status_code
(  rental_status_code           varchar(8),
   rental_status_descript       varchar(10),
   primary key (rental_status_code)
) ENGINE = InnoDB;

Edited by Dani: Fixed code tags

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by griswolf
0

Is your search engine broken?

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html:

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to error 150, that means a foreign key definition would be incorrectly formed for the altered table. You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server.

Edited by griswolf: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.