MySQL Error with CREATE TABLE

Question

I am new to creating/testing/working with MySQL. In this I have created a database and have used the command to USE the Database, however when trying to create a table, Error Code:1064 keeps coming up.

I have no previous tables in this database, this would be the first table.

I am unsure where the error is and would greatly appreciate it if someone could help me identiy the error and the reason why?

CREATE TABLE Customers(
customerNumber INT NOT NULL,
firstName VARCHAR(60),
lastName VARCHAR(60),
address VARCHAR(50) NOT NULL,
city VARCHAR(20) NOT NULL,
state ENUM('QLD','VIC','NSW','WA','TAS','NT','SA') NOT NULL,
postCode INT(4) NOT NULL,
region VARCHAR(60),
email VARCHAR(254),
PRIMARY KEY(customerNumber),
FOREIGN KEY(customerNumber)
);

Remove the foreign key reference. – Tim Biegeleisen May 19 '18 at 23:49 — Tim Biegeleisen, May 19 '18 at 23:49

score 1 · Answer 1 · answered May 19 '18 at 23:48

1

A foreign key must reference another table.

The other table must exist before you can reference it in a foreign key.

I contributed to a checklist for foreign keys in this post: https://stackoverflow.com/a/4673775/20860

answered May 19 '18 at 23:48

Bill Karwin

538,548
86
673
828

Thank you so much, I took out the foreign key constraint and the table was created. I will add the foreign key back after I have created the tables. Thank you again – Winnie May 19 '18 at 23:52

MySQL Error with CREATE TABLE

1 Answers1