Creating table within mysql db, ERROR 1064 (42000)?

Question

**Am working with MySQL 8.0 Command Line Client

I am trying to create a general table in mysql db but keep getting the following error:

"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 'AUTO_INCREMENT, ManufacturerPTNO VARCHAR(40) NOT NULL, QuantityAvailable12am I' at line 2"

I have look through the manual but haven't found anything that helps with this issue; have also tried removing the index (id) column and assigning it as the KEY

CREATE TABLE quantity ( 
    id INT NOT NULL AUTO_INCREMENT, 
    ManufacturerPTNO VARCHAR(40) NOT NULL, 
    QuantityAvailable12am INT, 
    QuantityAvailable01am INT, 
    PRIMARY KEY (ManufacturerPTNO),
    );

Possible duplicate of [How can I fix MySQL error #1064?](https://stackoverflow.com/questions/23515347/how-can-i-fix-mysql-error-1064) — Richard, Jun 11 '19 at 15:25
`PRIMARY KEY (ManufacturerPTNO),` notice the extra comma i voteclose this as a simple typographical error. — Raymond Nijland, Jun 11 '19 at 15:26

Jimish Gamit · Accepted Answer · 2019-06-11T15:44:06.790

0

CREATE TABLE quantity ( 
    id INT NOT NULL AUTO_INCREMENT, 
    ManufacturerPTNO VARCHAR(40) NOT NULL, 
    QuantityAvailable12am INT, 
    QuantityAvailable01am INT, 
    PRIMARY KEY (id)
    );

Primary key should be id

Updated answer as per you comment

CREATE TABLE quantity ( 
    id INT NOT NULL AUTO_INCREMENT, 
    ManufacturerPTNO VARCHAR(40) NOT NULL, 
    QuantityAvailable12am INT, 
    QuantityAvailable01am INT, 
    PRIMARY KEY (ManufacturerPTNO),
    UNIQUE KEY (id)
    );

edited Jun 11 '19 at 15:44

answered Jun 11 '19 at 15:30

Jimish Gamit

1,014
5
15

Yes this works but I need the PTNO to be the primary key so connect to another table – saddle_fish Jun 11 '19 at 15:36
Updated answer as per you comment – Jimish Gamit Jun 11 '19 at 15:44
Thanks for the help! – saddle_fish Jun 11 '19 at 16:17
Appreciate if you accept the answer if it worked for you, – Jimish Gamit Jun 11 '19 at 16:23

score 0 · Answer 2 · answered Jun 11 '19 at 16:10

0

CREATE TABLE quantity ( 
id INT NOT NULL AUTO_INCREMENT, 
ManufacturerPTNO VARCHAR(40) NOT NULL, 
QuantityAvailable12am INT, 
QuantityAvailable01am INT, 
PRIMARY KEY (ManufacturerPTNO),  <--- remove comma from here
);

and make PRIMARY KEY(id);

answered Jun 11 '19 at 16:10

Kvyas

21
6

score 0 · Answer 3 · answered Jul 28 '20 at 00:31

0

I had the same error message, after a long time I realized that I was trying to create a table by name "order" and that was the problem.As soon as I changed the name it worked!!!

answered Jul 28 '20 at 00:31

Almir Puglia

21
5

Creating table within mysql db, ERROR 1064 (42000)?

3 Answers3