I was trying to make a mysql database table with the following query. But it shows the error as given below. Table is created when I change the column name from default to default1. It seems the we cannot use the keyword 'default' as a column name. I need to use default itself as the column name. Any help appreciated.
mysql> CREATE TABLE testtable (
-> id smallint(5) unsigned NOT NULL auto_increment,
-> pos tinyint(3) unsigned default '0',
-> name char(255) default NULL,
-> type tinyint(1) unsigned default '1',
-> options char(255) default NULL,
-> default char(255) default NULL,
-> validation tinyint(1) unsigned default '0',
-> enable tinyint(1) unsigned default '1',
-> PRIMARY KEY (id)
-> ) TYPE=MyISAM;
ERROR 1064: You have an error in your SQL syntax near 'default char(255) default NULL,
validation tinyint(1) unsigned default '0',
' at line 7
I've run into this problem before in trying to create table names that overlap with MySQL keywords (e.g., table creation attempt with column name "desc" creates problems). My advice ... try to avoid a column name of "default" (or, at the very least, make it less ambiguous --- e.g., "is_default", etc.)
As others already mentioned this occurs because a MySQL keyword is used as field name. The best way would be to use a slightly modified name like SamBox mentioned for example. Otherwise you could use backticks to quote the name.
`default` char(255) default NULL,
▌Sitemeer.com - Is your site up?
▌Multi-Location Service Availability Check ● the only with support for HTTPS & IDN