Results 1 to 9 of 9
  1. #1

    mysql cannot create column named default

    Hello All,

    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

  2. #2
    Join Date
    Sep 2004
    Location
    Flint, Michigan
    Posts
    5,765
    Thread moved, and why do you have to use the name default?
    Mike from Zoodia.com
    Professional web design and development services.
    In need of a fresh hosting design? See what premade designs we have in stock!
    Web design tips, tricks, and more at MichaelPruitt.com

  3. #3
    Add quotes round the column name -> `default`
    Dyslexics Have More Fnu

  4. #4
    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.)

  5. #5
    Join Date
    Aug 2001
    Posts
    5,068
    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 ● yes, we do HTTPS & IDN!


  6. #6
    Join Date
    Jul 2006
    Location
    India
    Posts
    237
    When you are using ALTER table command try to use the new field name and old field name in singl e quotes.I think this may be help for this

  7. #7
    Join Date
    Aug 2001
    Posts
    5,068
    Quote Originally Posted by creativeartist
    try to use the new field name and old field name in singl e quotes.
    Correct, however not quotes (') but backticks (`).
    Sitemeer.com - Is your site up?
    Multi-Location Service Availability Check ● yes, we do HTTPS & IDN!


  8. #8
    Thanks for all your inputs.

    Dollar, its not written by me, but already written by one thirdparty software.

  9. #9
    Join Date
    Oct 2004
    Location
    UK
    Posts
    487
    As stated previously, the error is because default is one of MySQL's reserved words.

    See here for more information on reserved words: http://dev.mysql.com/doc/refman/5.0/...ved-words.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •