7

this is some simple code but I just don't know why I can't use this word as the entity of the table

CREATE TABLE IF NOT EXISTS users(
key INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
);

I realized I can't use "key" if I use key the mysql will ask me to check the syntax but if I use "id" or any others the table will be created.

Anyone know how I can create the entity name into key? Not something important since I can just use id instead of key but since I found this error I wonna see if there's a way to get it work.

John Woo
  • 258,903
  • 69
  • 498
  • 492
Tsuna
  • 2,098
  • 6
  • 24
  • 46
  • 1
    I strongly encourage you to find more meaningful names than SQL reserved words. Perhaps `UserId` would be a good name -- and to the casual observer, it is much more informative. – Gordon Linoff Mar 31 '13 at 01:04
  • possible duplicate of [Why can't I use certain words as table/column names in MySQL?](http://stackoverflow.com/questions/23446377/why-cant-i-use-certain-words-as-table-column-names-in-mysql) – Bernhard Barker May 03 '14 at 17:01
  • @GordonLinoff Your Comment is naïve. There are over a thousand words reserved by various database systems when I last did a tally. It is not at all obvious when devising a name whether it might collide with a reserved word. – Basil Bourque May 22 '19 at 14:15
  • 1
    @BasilBourque . . . `KEY` is part of the SQL Syntax -- think `PRIMARY KEY`. There is nothing naive about thinking that people creating tables in SQL should know the basic syntactic elements. – Gordon Linoff May 22 '19 at 14:40

2 Answers2

17

You can still use key if you want to. Just wrap it with backtick,

CREATE TABLE IF NOT EXISTS users
(
    `key` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
);

but as an advise, refrain from using any reserved keyword to avoid future problems. :)

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • ah alright that worked...I tried \ thought that might be how to do it since in php \ escapes the character hahah....thanks a lot ^_^ do learn something new everyday – Tsuna Mar 31 '13 at 01:00
4

Trailing Underscore

Simple solution: Add a trailing underscore to every name.

The SQL spec SQL:2011 explicitly promises to never use a trailing underscore on any keyword, neither now nor in the future.

Example: key_

See my answer to a similar question, h2 database column name is reserved word.

Community
  • 1
  • 1
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154