Issue of creating table in MySql

Question

I am trying to execute this query:

create table order_details(
    Order_id int PRIMARY KEY AUTO_INCREMENT,
    Book_Id int, 
    Cust_Name varchar(50),
    Phone_No int,
    Address varchar(100),
    Order_Date DATE,
    Quantity int,
    FOREIGN KEY(Book_Id) REFERENCES book(Book_Id));

But the result is a MySQL Error 1005:

ERROR 1005 (HY000): Can't create table 'bookstore.order_details' (errno: 150)

Do you have a `book` table, does it have a column named `Book_Id`, and is its column type `int`? — Chris Forrence, Aug 22 '17 at 15:23
[Check Here. I think the issue is same.](https://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150) — Shafikul Islam, Aug 22 '17 at 15:24
@RitwikSen yes i do have table named Book in same database and also the field into named Book_Id with type int — Jeet Patel, Aug 22 '17 at 15:25
@JeetPatel : Can you share the create table script for `Book` table. — Ritwik Sen, Aug 22 '17 at 15:27
Share us the table book since the error showing some error about that table. — Noob, Aug 22 '17 at 15:44

score 1 · Accepted Answer · answered Aug 22 '17 at 16:02

create table order_details(
   Order_id int PRIMARY KEY AUTO_INCREMENT,
   Book_Id int, 
   Cust_Name varchar(50),
   Phone_No int,
   Address varchar(100),
   Order_Date DATE,
   Quantity int,
FOREIGN KEY(Book_Id) REFERENCES book(Book_Id));

Error (150) is being thrown because mysql is unable to reference the specified foreign key. This can be caused for a number of reasons however, I recommend taking the following steps to troubleshoot this.

A common issue that causes this error to become evasive is when you have not performed USE my_database_name; before executing the query. It fails because the context is either wrong or absent.

1.) Revise your query by adding in the name of the database in the reference

Example: FOREIGN KEY(`Book_Id`) REFERENCES `my_database_name`.`book`(`Book_Id`));

2.) Take a look at the book table and make sure `Book_Id` is the correct type (int) and is named exactly as you reference it. Perform the following queries and you may find your answer:

SELECT `b`.`Book_Id` FROM `book` AS `b`;
EXPLAIN `book`;

Selecting Book_Id from the referenced table will rule out typos in field naming. Explain will reveal the value type & key information that should help ensure consistency between foreign relations when investigating issues like this.

score 0 · Answer 2 · answered Aug 22 '17 at 15:31

0

You might not be having a 'book' table in your database or if you have one then there might not be a column named 'Book_id'

answered Aug 22 '17 at 15:31

Chayan Bansal

1,857
1
13
23

Issue of creating table in MySql

2 Answers2