Repository: tobymao/sqlglot
Pull request: Feat(mysql): add support for the UNIQUE KEY constraint
Fixes #1707 

Problem statement:
Issues parsing MySQL with UNIQUE constraints
After creating a table in mysql, and getting the create sql string with `SHOW CREATE TABLE`, sqlglot is unable to parse the returned string.

I expect that sqlglot to correctly parse valid MySQL strings.

1) Create a MySQL table:
```
CREATE TABLE foo (
  id CHAR(36) DEFAULT (UUID()) UNIQUE,
  created_at TIMESTAMP,
  PRIMARY KEY (id)
)
```

2) Get create table sql:
```
SHOW CREATE TABLE foo

> CREATE TABLE `foo` (
  `id` char(36) NOT NULL DEFAULT (uuid()),
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

```
3) Parse string:
```
sql = """CREATE TABLE `foo` (
  `id` char(36) NOT NULL DEFAULT (uuid()),
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci"""

sqlglot.parse_one(sql, "mysql")

>>> ParseError: Expecting ). Line 6, Col: 12.
  T NULL DEFAULT (uuid()),
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
```

If you remove the `UNIQUE KEY` line the string will parse.

```
sql = """CREATE TABLE `foo` (
  `id` char(36) NOT NULL DEFAULT (uuid()),
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci"""

sqlglot.parse_one(sql, "mysql")

>>> (CREATE this: 
  (SCHEMA this: 
    (TABLE this: 
      (IDENTIFIER this: foo, quoted: True)), expressions: 
    (COLUMNDEF this: 
      (IDENTIFIER this: id, quoted: True), kind: 
      (DATATYPE this: Type.CHAR, expressions: 
        (DATATYPESIZE this: 
          (LITERAL this: 36, is_string: False)), nested: False, prefix: False), constraints: 
      (COLUMNCONSTRAINT kind: 
        (NOTNULLCOLUMNCONSTRAINT )), 
      (COLUMNCONSTRAINT kind: 
        (DEFAULTCOLUMNCONSTRAINT this: 
          (PAREN this: 
            (ANONYMOUS this: uuid))))), 
    (COLUMNDEF this: 
      (IDENTIFIER this: created_at, quoted: True), kind: 
      (DATATYPE this: Type.TIMESTAMP), constraints: 
      (COLUMNCONSTRAINT kind: 
        (NOTNULLCOLUMNCONSTRAINT allow_null: True)), 
      (COLUMNCONSTRAINT kind: 
        (DEFAULTCOLUMNCONSTRAINT this: 
          (NULL )))), 
    (PRIMARYKEY expressions: 
      (IDENTIFIER this: id, quoted: True))), kind: TABLE, properties: 
  (PROPERTIES expressions: 
    (ENGINEPROPERTY this: 
      (IDENTIFIER this: InnoDB, quoted: False)), 
    (CHARACTERSETPROPERTY this: 
      (VAR this: utf8mb4), default: True), 
    (COLLATEPROPERTY this: 
      (IDENTIFIER this: utf8mb4_0900_ai_ci, quoted: False))))
```
How can I parse sql string with a `UNIQUE` key? 


Thank you in advance for any help.

Hints from issue discussion:

Commit message: Feat(mysql): add support for the UNIQUE KEY constraint

Patch to review:
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index db1fa5005a..b74e369a23 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1451,7 +1451,7 @@ class PrimaryKey(Expression):
 
 
 class Unique(Expression):
-    arg_types = {"expressions": True}
+    arg_types = {"this": False, "expressions": True}
 
 
 # https://www.postgresql.org/docs/9.1/sql-selectinto.html
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 1d5f5f3eb3..1515e5d1bc 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1772,8 +1772,10 @@ def primarykey_sql(self, expression: exp.ForeignKey) -> str:
         return f"PRIMARY KEY ({expressions}){options}"
 
     def unique_sql(self, expression: exp.Unique) -> str:
+        this = self.sql(expression, "this")
+        this = f"{this} " if this else ""
         columns = self.expressions(expression, key="expressions")
-        return f"UNIQUE ({columns})"
+        return f"UNIQUE {this}({columns})"
 
     def if_sql(self, expression: exp.If) -> str:
         return self.case_sql(
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 6afc11c01b..49c02b41cc 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3371,9 +3371,11 @@ def _parse_unnamed_constraint(
         return self.CONSTRAINT_PARSERS[constraint](self)
 
     def _parse_unique(self) -> exp.Expression:
+        this = self._match_text_seq("KEY") and self._parse_id_var()
         if not self._match(TokenType.L_PAREN, advance=False):
             return self.expression(exp.UniqueColumnConstraint)
-        return self.expression(exp.Unique, expressions=self._parse_wrapped_id_vars())
+
+        return self.expression(exp.Unique, this=this, expressions=self._parse_wrapped_id_vars())
 
     def _parse_key_constraint_options(self) -> t.List[str]:
         options = []
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index be7887e8b3..a80153b552 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -6,6 +6,10 @@ class TestMySQL(Validator):
     dialect = "mysql"
 
     def test_ddl(self):
+        self.validate_identity(
+            "INSERT INTO x VALUES (1, 'a', 2.0) ON DUPLICATE KEY UPDATE SET x.id = 1"
+        )
+
         self.validate_all(
             "CREATE TABLE z (a INT) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin COMMENT='x'",
             write={
@@ -21,10 +25,6 @@ def test_ddl(self):
                 "mysql": "CREATE TABLE t (c DATETIME DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()) DEFAULT CHARACTER SET=utf8 ROW_FORMAT=DYNAMIC",
             },
         )
-        self.validate_identity(
-            "INSERT INTO x VALUES (1, 'a', 2.0) ON DUPLICATE KEY UPDATE SET x.id = 1"
-        )
-
         self.validate_all(
             "CREATE TABLE x (id int not null auto_increment, primary key (id))",
             write={
@@ -37,6 +37,12 @@ def test_ddl(self):
                 "sqlite": "CREATE TABLE x (id INTEGER NOT NULL)",
             },
         )
+        self.validate_all(
+            "CREATE TABLE `foo` (`id` char(36) NOT NULL DEFAULT (uuid()), PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`))",
+            write={
+                "mysql": "CREATE TABLE `foo` (`id` CHAR(36) NOT NULL DEFAULT (UUID()), PRIMARY KEY (`id`), UNIQUE `id` (`id`))",
+            },
+        )
 
     def test_identity(self):
         self.validate_identity("SELECT CURRENT_TIMESTAMP(6)")


Code review comments:
diff_hunk: @@ -3371,9 +3371,11 @@ def _parse_unnamed_constraint(
         return self.CONSTRAINT_PARSERS[constraint](self)
 
     def _parse_unique(self) -> exp.Expression:
+        this = self._match_text_seq("KEY") and self._parse_id_var()
         if not self._match(TokenType.L_PAREN, advance=False):
             return self.expression(exp.UniqueColumnConstraint)
-        return self.expression(exp.Unique, expressions=self._parse_wrapped_id_vars())
+
+        return self.expression(exp.Unique, this=this, expressions=self._parse_wrapped_id_vars())
path: sqlglot/parser.py
text: @author:
Maybe we can use a `Schema` expression here instead of both `this` and `expressions`?

@author:
Also, @user1 seems like we have both a `UniqueColumnConstraint` and a `Unique` expression. Should we get rid of the latter for example and represent the concept with just a single expression type?

@user1:
sure

@author:
Ok done