MySQL 150: Foreign key constraint is incorrectly formed

Question

When I import the file to phpmyadmin I'm getting this err :

#1005 - Can't create table `test_db`.`part_order` (errno: 150 "Foreign key constraint is incorrectly formed")

I checked the syntax many times but I can't see what's wrong. I tried to use INDEX and CONSTRAINT but no success. Any help will be appreciated.

CREATE TABLE IF NOT EXISTS `all_products` (   
  `product_id` int(10) NOT NULL AUTO_INCREMENT,   
  `product_name` VARCHAR(100)  NOT NULL,  
  `product_price` decimal(10,2) NOT NULL,   
  `product_description` VARCHAR(1000) NOT NULL,  
  PRIMARY KEY(`product_id`, `product_name`)   
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `product_color` (   
  `color_id` int(10) NOT NULL AUTO_INCREMENT,   
  `product_id` INT(10) NOT NULL,   
  `color_name` VARCHAR(100) NOT NULL,   
  PRIMARY KEY(`color_id`, `color_name`),   
  FOREIGN KEY(`product_id`) REFERENCES all_products(`product_id`)   
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `users` (   
  `user_id` INT(10) NOT NULL AUTO_INCREMENT,   
  `first_name` VARCHAR(100) NOT NULL,   
  `last_name` VARCHAR(100) NOT NULL,   
  `email` VARCHAR(100) NOT NULL,   
  `password` VARCHAR(200) NOT NULL,   
  `address` VARCHAR(200) NOT NULL,   
  `city` VARCHAR(100) NOT NULL,   
  `post_code` VARCHAR(8) NOT NULL,   
  PRIMARY KEY(`user_id`)   
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `part_order` (   
  `order_number` INT(10) NOT NULL ,   
  `product_name` VARCHAR(100) NOT NULL,   
  `color_name` VARCHAR(100) NOT NULL,   
  `qty` INT(10) NOT NULL,   
  PRIMARY KEY(`order_number`),   
  INDEX(`order_number`),   
  CONSTRAINT FOREIGN KEY(`product_name`) REFERENCES
    all_products(`product_name`) ON DELETE CASCADE ON UPDATE CASCADE,   
  CONSTRAINT FOREIGN KEY(`color_name`) REFERENCES
    product_color(`color_name`) ON DELETE CASCADE ON UPDATE CASCADE   
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `orders` (   
  `order_number` INT(10) NOT NULL,   
  `user_id` INT(10) NOT NULL,   
  Date DATETIME DEFAULT CURRENT_TIMESTAMP,   
  `total_cost` INT(10) NOT NULL,   
  `status` VARCHAR(50) NOT NULL,   
  PRIMARY KEY(`status`),   
  CONSTRAINT FOREIGN KEY(`order_number`) REFERENCES
    part_order(`order_number`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FOREIGN KEY(`user_id`) REFERENCES
    users(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
)

Answer 1

MySQL requires indexes on the referenced columns (product_name and color_name). Add INDEX(product_name) to the all_products table and INDEX(color_name) to the product_color table

CREATE TABLE IF NOT EXISTS `all_products` (   
  `product_id` int(10) NOT NULL AUTO_INCREMENT,   
  `product_name` VARCHAR(100)  NOT NULL,  
  `product_price` decimal(10,2) NOT NULL,   
  `product_description` VARCHAR(1000) NOT NULL,  
  PRIMARY KEY(`product_id`, `product_name`),
  INDEX(`product_name`)   
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

From the docs:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.