?
Solved

MySQL Create Table w/Constraints

Posted on 2008-11-06
1
Medium Priority
?
1,817 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:Messenger569
1 Comment
 
LVL 18

Accepted Solution

by:
philipjonathan earned 2000 total points
ID: 22902058
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month15 days, 16 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question