Repository navigation
MySQL (optional) Index Names #1570
Description
Steps to reproduce/Actual Behavior:
CCJSqlParserUtil.parse("CREATE TABLE table1 (col1 type1, col2 type2 UNIQUE, CONSTRAINT my_constraint UNIQUE KEY index_name (col1))")
JSQLParserException: Was expecting "("
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
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
Activity
manticore-projects commentedon Jun 19, 2022
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
minleejae commentedon Sep 2, 2026
This remains reproducible on
5.4-SNAPSHOTat9a32ff568f580178d9a27b101596aa43eb45d0b7. I also validated the examples against MySQL 8.4.11.These server-valid forms still throw
ParseExceptionat the separate index name:The first fails at
index_name; the second fails atfk_parent_idx. Both statements executed successfully onmysql:8.4(server 8.4.11), andSHOW CREATE TABLEconfirmed the resulting UNIQUE/FK metadata. A fix should keep the constraint symbol and optional index name as distinct fields in the public model.OpenAI Codex was used to rerun the parser/server comparison; the result was manually checked against the MySQL 8.4
CREATE TABLEgrammar.