CODEWITHSUNDEEP
mysql
user2656114
user2656114

Reputation: 970

ERROR 1064 (42000): You have an error in your SQL syntax

Trying to create a table in MySQL using:

 CREATE TABLE users (  
    id INT(5) NOT NULL ZEROFILL AUTO_INCREMENT PRIMARY KEY ,  
    passport VARCHAR(250) NOT NULL ,
    nickname VARCHAR(250) NOT NULL ,
    mail VARCHAR(250) NOT NULL ,  
    password VARCHAR(250) NOT NULL ,  
    age INT(2) NOT NULL ,
    sex VARCHAR(250) NOT NULL ,  
    is_registered VARCHAR(1) NOT NULL DEFAULT 1 ,
    is_registered_timestamp TIMESTAMP NOT NULL ZEROFILL , 
    is_admin VARCHAR(1) NOT NULL DEFAULT 0 ,
    is_admin_timestamp TIMESTAMP NOT NULL ZEROFILL ,
    is_banned VARCHAR(1) NOT NULL DEFAULT 0 ,
    is_banned_timestamp TIMESTAMP NOT NULL ZEROFILL
    );

Getting the error message:

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 'ZEROFILL AUTO_INCREMENT PRIMARY KEY ,
`passport` VARCHAR(250) NOT NULL ,
`nick' at line 2

What does this mean? I am new to MySQL and have read as much as possible to try and understand the different data types, and assume I've used the correct data types so can't understand what this error message is trying to say.

Upvotes: 2

Views: 6351

Answers (2)

miltos
miltos

Reputation: 1019

ZEROFILL is property of datatype

change the order of ZEROFILL and NOT NULL like the query thats follow

CREATE TABLE `users` (  
`id` INT(5) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY ,  
...

for reference read this

Upvotes: 3

Ján Srniček
Ján Srniček

Reputation: 525

First advice,this look like you are generating DDL from MySQL workbench, i was trying that too, but it also give me allways errors like that. Try to do model of that table(database) in proper modeling tool tike Toad or something like that ,i tried that and it worked every time.

To that syntax, it look like ,the compiler is not recognizing some key words like ZEROFILL,AUTO INCREMENT. Auto increment can be also property of whole database(scheme) so you can turn it on in global,no reason for setting it for every table. If i remember right,the bigges problem in MySQL, and using generated DDL were that [`] symbol , try to remove them.

Upvotes: 0

Related Questions