Getting a Cannot add Foreign Key Constraint MySQL

Question

I am having some trouble with phpMyAdmin and MySQL. All of the tables load just fine except for the order table. No matter if I do it all at once, or one table at a time, I get a #1215 - Cannot add foreign key constraint.

This happens for the Orders table only and the Customer_Number attribute. What in the world am I missing here. Thanks in advance.

-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Apr 19, 2015 at 01:22 AM
-- Server version: 5.6.21
-- PHP Version: 5.6.3

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
SET foreign_key_checks=0;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;


-- Database: `popcorn`


-- --------------------------------------------------------


-- Table structure for table `customer`


CREATE TABLE IF NOT EXISTS `customer` (
  `Scout_Number` int(10) NOT NULL,
  `Customer_Number` int(10) NOT NULL,
  `Fname` varchar(15) NOT NULL,
  `Lname` varchar(15) NOT NULL,
  `House_Number` int(7) NOT NULL,
  `Street` varchar(15) NOT NULL,
  `City` varchar(15) NOT NULL,
  `State` char(2) NOT NULL,
  `Zip` int(5) NOT NULL,
  `Phone` int(10) NOT NULL,
  `Email` varchar(30) NOT NULL,
  PRIMARY KEY (Scout_Number, Customer_Number)


) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------


-- Table structure for table `den`


CREATE TABLE IF NOT EXISTS `den` (
  `Den_Number` int(3) NOT NULL,
  `User_Name` varchar(8) NOT NULL,
  `Fname` varchar(15) NOT NULL,
  `Lname` varchar(15) NOT NULL,
  `Phone` int(10) NOT NULL,
  `Email` varchar(30) NOT NULL,
  `Den_City` varchar(15) NOT NULL,
  `Sales_Goal` int(10) NOT NULL,
  `Den_Sales_Total` decimal(10,2) NOT NULL,
  `Den_State` char(2) NOT NULL,
  PRIMARY KEY (Den_Number)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- Dumping data for table `den`




-- Table structure for table `order`


CREATE TABLE IF NOT EXISTS `order` (
  `Order_Number` int(10) NOT NULL,
  `Customer_Number` int(10) NOT NULL,
  `Donation` decimal(5,2) NOT NULL,
  `Order_Total` decimal(5,2) NOT NULL,
  `Payment_Status` char(1) NOT NULL,
  `Payment_Type` varchar(10) NOT NULL,
  `Date` date NOT NULL,
  `Delivery_Status` char(1) NOT NULL,
  PRIMARY KEY (Order_Number),
  FOREIGN KEY (Customer_Number) REFERENCES customer(Customer_Number)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------


-- Table structure for table `order_product`


CREATE TABLE IF NOT EXISTS `order_product` (
  `Order_Number` int(10) NOT NULL,
  `Product_Number` int(10) NOT NULL,
  PRIMARY KEY (Order_Number, Product_Number)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------


-- Table structure for table `product`


CREATE TABLE IF NOT EXISTS `product` (
  `Product_Number` int(10) NOT NULL,
  `Product_Name` varchar(15) NOT NULL,
  `Description` text NOT NULL,
  `Image` blob NOT NULL,
  `Price` decimal(5,2) NOT NULL,
  PRIMARY KEY (Product_Number)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------


-- Table structure for table `scout`


CREATE TABLE IF NOT EXISTS `scout` (
  `Scout_Number` int(10) NOT NULL,
  `User_Name` char(8) NOT NULL,
  `Fname` varchar(15) NOT NULL,
  `Lname` varchar(15) NOT NULL,
  `Sales_Goal` decimal(10,2) NOT NULL,
  `Prize_Progress` int(10) NOT NULL,
  `Den_Number` int(3) NOT NULL,
  PRIMARY KEY (Scout_Number),
  FOREIGN KEY (Den_Number) REFERENCES den(Den_Number)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Answer 1

Columns referenced in a foreign key have to be indexed. You don't have an index on Customer_Number in the customer table. Either change the order of the columns in the composite primary key to(Customer_Number, Scout_Number)or add an additional key just on theCustomer_Number` column.

Note, however, that having a foreign key pointing to a non-unique column is a MySQL extension to SQL, and likely to be a bad idea. See Can a foreign key reference a non-unique index?. I wonder why the primary key of the customer table isn't just Customer_Number.