MySQL Create Table w/Constraints

I'm running each statement of this code independently. When I try to create the Orders table the way it is now, it throws the following error:

ERROR [HY000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.27-community-nt]Can't create table '.\webarch\orders.frm' (errno: 150)
Finished executing selected script from CreateJohnsonMotorsports.sql

DROP TABLE Orders;
DROP TABLE Customers;
DROP TABLE Employees;
DROP TABLE Products;
 
CREATE TABLE Customers (
	CustomerID INT PRIMARY KEY AUTO_INCREMENT,
	CustomerLastName VARCHAR (30) NOT NULL,
	CustomerFirstName VARCHAR (30) NULL,
	CustomerAddress VARCHAR (30) NULL,
	CustomerCity VARCHAR (30) NULL,
	CustomerState VARCHAR (2) NULL,
	CustomerZip VARCHAR (10) NULL,
	CustomerPhone VARCHAR (20) NULL,
    CustomerUsername VARCHAR (10) NULL,
    CustomerPassword VARCHAR (20) NULL) ENGINE=INNODB;
    
CREATE TABLE Employees (
	EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
	EmployeeLastName VARCHAR (30) NOT NULL,
	EmployeeFirstName VARCHAR (30) NULL,
    EmployeeUsername VARCHAR (10) NULL,
    EmployeePassword VARCHAR (20) NULL,
    IsManager BOOLEAN NULL) ENGINE=INNODB;
    
CREATE TABLE Products (
	ProductID INT PRIMARY KEY AUTO_INCREMENT,
	ProductName VARCHAR (50) NULL,
	ProductDescription VARCHAR (100) NULL,
	ProductQOH INT NOT NULL,
	ProductColor VARCHAR (20) NULL,	
	ProductSize VARCHAR (5) NULL,	
	ProductPrice DECIMAL(30,2) NULL) ENGINE=INNODB;
 
CREATE TABLE Orders (
	OrderID INT PRIMARY KEY AUTO_INCREMENT,
	ProductID INT NOT NULL,
	EmployeeID INT NULL,
	CustomerID INT NOT NULL,
	OrderDate DATETIME NOT NULL,
	QuantityOrdered INT NOT NULL,
	SalePrice DECIMAL(30,2) NULL,
	CONSTRAINT FK_Order_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
	CONSTRAINT FK_Order_EmployeeID FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),
	CONSTRAINT FK_Order_Product FOREIGN KEY (ProductID) REFERENCES Products(ProductID)) ENGINE=INNODB;

Open in new window

Messenger569Asked:
Who is Participating?
 
philipjonathanConnect With a Mentor Commented:
Check these 2 lines, the table name should be Customers and Employees (with 's')

CONSTRAINT FK_Order_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
CONSTRAINT FK_Order_EmployeeID FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.