MySQL (optional) Index Names

[Alletkla]

trafficstars

Steps to reproduce/Actual Behavior:

1. CCJSqlParserUtil.parse("CREATE TABLE table1 (col1 type1, col2 type2 UNIQUE, CONSTRAINT my_constraint UNIQUE KEY index_name (col1))")

JSQLParserException: Was expecting "("

2. CCJSqlParserUtil.parse("CREATE TABLE table1 (col1 type1, col2 type2 UNIQUE, CONSTRAINT UNIQUE KEY (col1))")

JSQLParserException: Was expecting one of: "ACTION", "ACTIVE" ... and many more Keywords without UNIQUE/FOREIGN but with PRIMARY, INDEX

3. CCJSqlParserUtil.parse("CREATE TABLE table1 (col1 type1, col2 type2 UNIQUE, CONSTRAINT my_constraint UNIQUE KEY (col1))")

index_name is the constraint name, but actually the index_name is left empty

for all CONSTRAINTS the same.

Expected behavior:

• parsing works according to reference
• for 3: make a difference between constraint name and key_name

create_definition: {
    col_name column_definition
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition
  | check_constraint_definition
}

Documentation MySQL 8.0 grammar part at the start and text under Heading: Indexes, Foreign Keys, and CHECK Constraints

If the clause is not given, or a symbol is not included following the CONSTRAINT keyword, MySQL automatically generates a constraint name

Environment: JSQLParser 4.4 on commit fc5a9a3dbb91e8e77122f953c2f29c0e028479d1

[manticore-projects]

Greetings,

thank your for the report. It contains all needed information and is a fine example, how an error report should look alike.

That said: those MySQL specific syntax are not supported. The support syntax are shown here: http://217.160.215.75:8080/jsqlformatter/syntax.html