ERROR 1064 (42000): You have an error in your SQL syntax at zero fill column?

Following is the error and it occurs when you implement ZEROFILL incorrectly−

mysql> create table DemoTable
   -> (
   -> StudentCode int(10) NOT NULL ZEROFILL AUTO_INCREMENT PRIMARY KEY
   -> );
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
)' at line 3

For correct implementation, use the below syntax −

Syntax

yourColumnName int(10) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY

Let us first create a table −

mysql> create table DemoTable
   -> (
   -> StudentCode int(10) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY
   -> );
Query OK, 0 rows affected (0.55 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values();
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.28 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.10 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+-------------+
| StudentCode |
+-------------+
| 0000000001  |
| 0000000002  |
| 0000000003  |
| 0000000004  |
| 0000000005  |
+-------------+
5 rows in set (0.00 sec)

AmitDiwan

Updated on: 13-Dec-2019

429 Views

Kickstart Your Career

Get certified by completing the course

Get Started