How do I escape reserved words used as column names? MySQL/Create Table
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;
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 `
character on various keyboard layouts is covered in this answer)
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)
You should use back tick character (`) eg:
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;