Got syntax error when I tried to create a table in a database

Question

I was trying to create a table in R Studio (on a local MySQL server) The code is:

create table conditions(
    condition varchar(255),
    explanation varchar(255),
    cid INT not null,
    PRIMARY KEY (cid)
);

And after running it I got

Show in New Window Error in .local(conn, statement, ...) : could not run statement: 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 'condition varchar(255), explanation varchar(255), cid INT not null, ' at line 2 Failed to execute SQL chunk

What could be the reason for this? I am confused since I could create other tables using the same way

I created other tables using the same syntax and those tables could be established

Answer 1

The word "condition" is a reserved word in MySQL, and you cannot use it as a table name or column name without enclosing it in backticks (`). Try changing the script to this:

   create table conditions(
        `condition` varchar(255),
        explanation varchar(255),
        cid INT not null,
        PRIMARY KEY (cid)
   );