CODEWITHSUNDEEP
mysql
JonasSH
JonasSH

Reputation: 206

Can't add column into table - mysql

I have a table called 'messages'. I wan't to add a column called 'key'.

When I try

ALTER TABLE messages ADD key BIGINT(20);

I get this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bigint(20)' at line 1

What am I doing wrong?

Upvotes: 2

Views: 2812

Answers (4)

Rahul
Rahul

Reputation: 77876

Problem is key is a reserve word in MySQL and thus needs to be escaped using backtique like

ALTER TABLE messages ADD `key`

(OR) using double quotes "" (ANSI92 standard) like

 ALTER TABLE messages ADD "key"

Better yet, stop using reservewords/keywords for table/column names (in fact for any DB object names)

Upvotes: 0

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476614

Syntax highlighting already shows the problem: key is a keyword (yeah I know that sounds funny). You can use backquotes to specify the column name:

ALTER TABLE messages ADD `key` BIGINT(20);
--                       ^   ^ backquotes

Mind that the backquotes are not part of the name of the column: the name of the column will be key. By using a backquote you state explicitly that you write a column name, not the keyword key.

Upvotes: 2

bigbounty
bigbounty

Reputation: 17368

Always add ` backquotes around keywords.

https://dev.mysql.com/doc/refman/5.7/en/keywords.html

Upvotes: 1

Bikash
Bikash

Reputation: 1938

Because key is a keyword in mysql. Reserved keywords

You can use 

ALTER TABLE messages ADD `key` BIGINT(20);

by escaping the word key you should be fine with this query.

Upvotes: 3

Related Questions