How do I escape reserved words used as column names? MySQL/Create Table

escaping, mysql

Solution

You can use double quotes if ANSI SQL mode is enabled

CREATE TABLE IF NOT EXISTS misc_info
  (
     id    INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
     "key" TEXT UNIQUE NOT NULL,
     value TEXT NOT NULL
  )
ENGINE=INNODB;

or the proprietary back tick escaping otherwise. (Where to find the

CREATE TABLE IF NOT EXISTS misc_info ( id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, `key` TEXT UNIQUE NOT NULL, value TEXT NOT NULL ) ENGINE=INNODB;


(Source: MySQL Reference Manual, 9.3 Reserved Words)

Problem

I am generating tables from classes in .NET and one problem is a class may have a field name `key` which is a reserved MySQL keyword. How do I escape it in a create table statement? (Note: The other problem below is text must be a fixed size to be indexed/unique) ``` create table if not exists misc_info ( id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, key TEXT UNIQUE NOT NULL, value TEXT NOT NULL)ENGINE=INNODB; ```

Original source