Using reserved words in column names

Question

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.

Answer 1

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. :)

• MySQL Reserved Keywords List

Answer 2

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.