MySQL CONSTRAINT FOREIGN KEY syntax

Question

I am new to MySQL and am messing with a database I created just to learn.

I created my first table and it worked OK:

mysql> CREATE TABLE Pizzas(ItemID INTEGER PRIMARY KEY, Size INTEGER, Price DECIMAL);
Query OK, 0 rows affected (0.90 sec)

then I tried to create another table using CONSTRAINT (I was simply copying the way foreign keys were created in a class I am taking) and I got an error:

mysql> CREATE TABLE Customers(CustomerID PRIMARY KEY, Name CHAR(20), ItemID INTEGER, CONSTRAINT FOREIGN KEY (ItemID) REFERENCES Pizzas (ItemID));

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY KEY, Name CHAR(20), ItemID INTEGER, CONSTRAINT FOREIGN KEY (ItemID) REFE' at line 1

I'm using a linux computer and my MySQL version is 5.5. Why am I getting this problem?

You don't have a data type (for example INTEGER) for CustomerID. Also, some advice: use VARCHAR(20) instead of CHAR(20) for the Name column, as CHAR(20) will be padded with spaces at the end. You won't see them, but they'll be there and they'll mess you up. — Ed Gibbs
– Ed Gibbs, Commented Oct 2, 2014 at 18:44

Rahul · Accepted Answer · 2014-10-02 18:45:17Z

2

your table definition is not correct. you are missing the datatype in CustomerID PRIMARY KEY. It should be

CREATE TABLE Customers(CustomerID int not null PRIMARY KEY,
                                  <-- Here 
Name CHAR(20), ItemID INTEGER, 
CONSTRAINT FOREIGN KEY (ItemID) REFERENCES Pizzas (ItemID));

answered Oct 2, 2014 at 18:45

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL CONSTRAINT FOREIGN KEY syntax

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related