2

I am creating this table into a MySql database:

CREATE TABLE actors (
  id        BigInt UNSIGNED NOT NULL AUTO_INCREMENT,
  sample_id VarChar(128) NOT NULL,
  `role`    Char(2) NOT NULL,
  wiews     VarChar(16),
  pid       VarChar(16),
  `name`    VarChar(128),
  address   VarChar(128),
  country   Char(3), 
  PRIMARY KEY (
      id
  )
) ;
ALTER TABLE actors COMMENT = '';

When I perform the above DDL statement I get the following 2 error messages:

Name "role" is a reserved keyword. You must use another name.
Name "name" is a reserved keyword. You must use another name.
Using the database default storage engine ("InnoDB").

So it means that using MySql I can't use the field-name role and name because are keyword reserved to MySql?

The problem is that another person give me the specific to create these tables (included the name of field) because I think that an application expects this field names.

So do you confirm that I can't create field with these 2 names?

Shadow
  • 33,525
  • 10
  • 51
  • 64
AndreaNobili
  • 40,955
  • 107
  • 324
  • 596
  • Tried on [Rextester](http://rextester.com/FKP63771) your query works – Stefano Zanini Mar 16 '17 at 15:48
  • @StefanoZanini I am using Database Workbench 5 and it is giving me this error message... – AndreaNobili Mar 16 '17 at 15:50
  • [MySQL 5.7 Reference Manual - Keywords and Reserved Words](https://dev.mysql.com/doc/refman/5.7/en/keywords.html) – roetnig Mar 16 '17 at 15:52
  • 1
    I executed your DDL statement and the table was created OK – roetnig Mar 16 '17 at 15:55
  • 2
    Are you sure that the error message comes from MySQL and not from the GUI that you use? If a field name is enclosed by backticks (\`) then even reserved words can be used as field names. In MySQL the error message would be: `ERROR 1064 (42000): You have an error in your SQL syntax near 'role...'` – Shadow Mar 16 '17 at 16:07
  • 1
    Not only that, but neither of these are reserved words. `name` is a keyword, but it's not reserved. `role` isn't even a keyword, there's no reason you shouldn't be able to use it. – Barmar Mar 16 '17 at 16:20
  • No error in MySQL says "You must use another name." Also, MySQL doesn't tell you when it's using the default storage engine. So these messages must be coming from the client. – Bill Karwin Mar 16 '17 at 16:25

1 Answers1

0

The error message in the question does not seem to come from MySQL, since in MySQL an error message regarding an illegal use of a keyword in an sql statement would say:

ERROR 1064 (42000): You have an error in your SQL syntax near 'role...'

Not to mention the fact that in the question both name and role field names are enclosed by backticks (`). Even reserved words can be used as an object identifier if it is enclosed by backticks.

As @St pointed out in the comments: the code runs in rextester

Therefore, I checked out Database Workbench 5 documentation and in the section on introducing the various editors it says:

The Object Editors will also check for reserved keywords and SQL 92/99 standard keywords and depending on your database engine capabilities, it will issue a warning or an error, before sending the create statements to the server.

Conclusion: your GUI checks the sql statement for reserved words and prevents the sql from executing. If you tried to execute it via another GUI or the CLI, then there would not be any error.

You may want to check out Database Workbench 5 documentation if this setting can be disabled or downgraded to warning.

Shadow
  • 33,525
  • 10
  • 51
  • 64