I need help creating the tables for my database, I've got all my tables to work except the first one. For some reason it says it is unable to create the first table and i get this error " ERROR 1005 (HY000): Can't create table 'jfunchio.rental' (errno: 150)" I was wondering if someone could help me figure out what is wrong with my create table query. Also if anyone has any tips to make my code better I would appreciate it.

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;

Recommended Answers

All 2 Replies

You have to define the referenced (foreign) tables first before you can define a table which contains a foreign key. So create the rental tables as the last one.

commented: Yes, that is the solution. +8

In others words rental can be created on once customer, movie and game tables are in existence.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.