Member Avatar for jfunchio

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;
  • 3 Contributors
  • 2 Replies
  • 268 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

Error 1005 (errno: 150) means MySQL failed while creating a foreign‑key relationship — not a mysterious disk problem. As and noted, the simplest cause is trying to create the child table (rental) before its parent tables exist. There are, however, several other common causes; the steps below show how to diagnose the exact problem and how to harden the schema.

Quick diagnosis (run these from mysql):

SHOW ENGINE INNODB STATUS\G
SHOW ERRORS;
SHOW CREATE TABLE customer\G
SHOW CREATE TABLE movie\G
SHOW CREATE TABLE game\G

To compare column definitions (types, length, charset, nullability) across parent/child use information_schema:

SELECT TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,CHARACTER_SET_NAME,COLLATION_NAME,IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='jfunchio' AND COLUMN_NAME IN ('customer_id','movie_id','game_id')
ORDER BY TABLE_NAME;

Things to check (common causes of errno 150)

  • Both tables must use InnoDB.
  • The referenced column must be indexed (PRIMARY or UNIQUE).
  • Data type and length must match exactly (for strings: type, length, character set and collation).
  • For numeric types, signed/unsigned must match.
  • If you use ON DELETE SET NULL, the child column must allow NULL.
  • Avoid reserved words (table named cast can be confusing — rename or quote it).

Suggested improvements (practical)

  • Use DATE or DATETIME for dates instead of VARCHAR.
  • Use DECIMAL(7,2) (or similar) for money, not numeric(2,2) which is likely too small.
  • Make many‑to‑many tables have composite keys (e.g., movie_cast primary key (movie_id, actor_id)).
  • Create parent tables first, then child tables; or create child table first but add FKs later with ALTER TABLE after parent exists.

If the error persists, paste the output of SHOW ENGINE INNODB STATUS\G and SHOW CREATE TABLE <table>\G; those lines point to the exact mismatched columns.

Recommended Answers

All 2 Replies

Member Avatar for smantscheff

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
Member Avatar for debasisdas

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.