Repository: tobymao/sqlglot

Issue:
Parser doesn't recognize a foreign-key-clause within a column definition after unique for sqlite
When parsing a column definition with a foreign-key-clause, the parser does not expect the key word REFERENCES after UNIQUE

**Fully reproducible code snippet**
```python
from sqlglot import parse_one
s = '''
CREATE TABLE "store"
(
    store_id         INTEGER
        primary key autoincrement,
    manager_staff_id INTEGER                            not null
        unique
        references staff
            on update cascade
)
'''
parse_one(s, read="sqlite")
```

```
ParseError                                Traceback (most recent call last)
Cell In[15], line 13
      1 from sqlglot import parse_one
      2 s = '''
      3 CREATE TABLE "store"
      4 (
   (...)
     11 )
     12 '''
---> 13 parse_one(s, read="sqlite")

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/__init__.py:139, in parse_one(sql, read, dialect, into, **opts)
    137     result = dialect.parse_into(into, sql, **opts)
    138 else:
--> 139     result = dialect.parse(sql, **opts)
    141 for expression in result:
    142     if not expression:

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/dialects/dialect.py:920, in Dialect.parse(self, sql, **opts)
    919 def parse(self, sql: str, **opts) -> t.List[t.Optional[exp.Expression]]:
--> 920     return self.parser(**opts).parse(self.tokenize(sql), sql)

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1416, in Parser.parse(self, raw_tokens, sql)
   1402 def parse(
   1403     self, raw_tokens: t.List[Token], sql: t.Optional[str] = None
   1404 ) -> t.List[t.Optional[exp.Expression]]:
   1405     """
   1406     Parses a list of tokens and returns a list of syntax trees, one tree
   1407     per parsed SQL statement.
   (...)
   1414         The list of the produced syntax trees.
   1415     """
-> 1416     return self._parse(
   1417         parse_method=self.__class__._parse_statement, raw_tokens=raw_tokens, sql=sql
   1418     )

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1485, in Parser._parse(self, parse_method, raw_tokens, sql)
   1482 self._tokens = tokens
   1483 self._advance()
-> 1485 expressions.append(parse_method(self))
   1487 if self._index < len(self._tokens):
   1488     self.raise_error("Invalid expression / Unexpected token")

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1717, in Parser._parse_statement(self)
   1714     return None
   1716 if self._match_set(self.STATEMENT_PARSERS):
-> 1717     return self.STATEMENT_PARSERS[self._prev.token_type](self)
   1719 if self._match_set(self.dialect.tokenizer.COMMANDS):
   1720     return self._parse_command()

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:785, in Parser.<lambda>(self)
    711 COLUMN_OPERATORS = {
    712     TokenType.DOT: None,
    713     TokenType.DCOLON: lambda self, this, to: self.expression(
   (...)
    744     ),
    745 }
    747 EXPRESSION_PARSERS = {
    748     exp.Cluster: lambda self: self._parse_sort(exp.Cluster, TokenType.CLUSTER_BY),
    749     exp.Column: lambda self: self._parse_column(),
   (...)
    775     "JOIN_TYPE": lambda self: self._parse_join_parts(),
    776 }
    778 STATEMENT_PARSERS = {
    779     TokenType.ALTER: lambda self: self._parse_alter(),
    780     TokenType.BEGIN: lambda self: self._parse_transaction(),
    781     TokenType.CACHE: lambda self: self._parse_cache(),
    782     TokenType.COMMENT: lambda self: self._parse_comment(),
    783     TokenType.COMMIT: lambda self: self._parse_commit_or_rollback(),
    784     TokenType.COPY: lambda self: self._parse_copy(),
--> 785     TokenType.CREATE: lambda self: self._parse_create(),
    786     TokenType.DELETE: lambda self: self._parse_delete(),
    787     TokenType.DESC: lambda self: self._parse_describe(),
    788     TokenType.DESCRIBE: lambda self: self._parse_describe(),
    789     TokenType.DROP: lambda self: self._parse_drop(),
    790     TokenType.GRANT: lambda self: self._parse_grant(),
    791     TokenType.INSERT: lambda self: self._parse_insert(),
    792     TokenType.KILL: lambda self: self._parse_kill(),
    793     TokenType.LOAD: lambda self: self._parse_load(),
    794     TokenType.MERGE: lambda self: self._parse_merge(),
    795     TokenType.PIVOT: lambda self: self._parse_simplified_pivot(),
    796     TokenType.PRAGMA: lambda self: self.expression(exp.Pragma, this=self._parse_expression()),
    797     TokenType.REFRESH: lambda self: self._parse_refresh(),
    798     TokenType.ROLLBACK: lambda self: self._parse_commit_or_rollback(),
    799     TokenType.SET: lambda self: self._parse_set(),
    800     TokenType.TRUNCATE: lambda self: self._parse_truncate_table(),
    801     TokenType.UNCACHE: lambda self: self._parse_uncache(),
    802     TokenType.UPDATE: lambda self: self._parse_update(),
    803     TokenType.USE: lambda self: self.expression(
    804         exp.Use,
    805         kind=self._parse_var_from_options(self.USABLES, raise_unmatched=False),
    806         this=self._parse_table(schema=False),
    807     ),
    808     TokenType.SEMICOLON: lambda self: self.expression(exp.Semicolon),
    809 }
    811 UNARY_PARSERS = {
    812     TokenType.PLUS: lambda self: self._parse_unary(),  # Unary + is handled as a no-op
    813     TokenType.NOT: lambda self: self.expression(exp.Not, this=self._parse_equality()),
   (...)
    817     TokenType.DPIPE_SLASH: lambda self: self.expression(exp.Cbrt, this=self._parse_unary()),
    818 }
    820 STRING_PARSERS = {
    821     TokenType.HEREDOC_STRING: lambda self, token: self.expression(
    822         exp.RawString, this=token.text
   (...)
    835     ),
    836 }

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1872, in Parser._parse_create(self)
   1869 self._match(TokenType.COMMA)
   1870 extend_props(self._parse_properties(before=True))
-> 1872 this = self._parse_schema(this=table_parts)
   1874 # exp.Properties.Location.POST_SCHEMA and POST_WITH
   1875 extend_props(self._parse_properties())

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:5426, in Parser._parse_schema(self, this)
   5424     return this
   5425 args = self._parse_csv(lambda: self._parse_constraint() or self._parse_field_def())
-> 5426 self._match_r_paren()
   5427 return self.expression(exp.Schema, this=this, expressions=args)

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:7222, in Parser._match_r_paren(self, expression)
   7220 def _match_r_paren(self, expression: t.Optional[exp.Expression] = None) -> None:
   7221     if not self._match(TokenType.R_PAREN, expression=expression):
-> 7222         self.raise_error("Expecting )")

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1529, in Parser.raise_error(self, message, token)
   1517 error = ParseError.new(
   1518     f"{message}. Line {token.line}, Col: {token.col}.\n"
   1519     f"  {start_context}\033[4m{highlight}\033[0m{end_context}",
   (...)
   1525     end_context=end_context,
   1526 )
   1528 if self.error_level == ErrorLevel.IMMEDIATE:
-> 1529     raise error
   1531 self.errors.append(error)

ParseError: Expecting ). Line 8, Col: 17.

    manager_staff_id INTEGER                            not null
        unique
        references staff
            on update cascade
)
In [16]: from sqlglot import parse_one
    ...: s = '''
    ...: CREATE TABLE "store"
    ...: (
    ...:     store_id         INTEGER
    ...:         primary key autoincrement,
    ...:     manager_staff_id INTEGER                            not null
    ...:         references staff
    ...:             on update cascade
    ...: )
    ...: '''
    ...: parse_one(s, read="sqlite")
    ...:
Out[16]:
Create(
  this=Schema(
    this=Table(
      this=Identifier(this=store, quoted=True)),
    expressions=[
      ColumnDef(
        this=Identifier(this=store_id, quoted=False),
        kind=DataType(this=Type.INT, nested=False),
        constraints=[
          ColumnConstraint(
            kind=PrimaryKeyColumnConstraint()),
          ColumnConstraint(
            kind=AutoIncrementColumnConstraint())]),
      ColumnDef(
        this=Identifier(this=manager_staff_id, quoted=False),
        kind=DataType(this=Type.INT, nested=False),
        constraints=[
          ColumnConstraint(
            kind=NotNullColumnConstraint()),
          ColumnConstraint(
            kind=Reference(
              this=Table(
                this=Identifier(this=staff, quoted=False)),
              options=[
                ON update CASCADE]))])]),
  kind=TABLE)
In [17]: from sqlglot import parse_one
    ...: s = '''
    ...: CREATE TABLE "store"
    ...: (
    ...:     store_id         INTEGER
    ...:         primary key autoincrement,
    ...:     manager_staff_id INTEGER                            not null
    ...:         unique
    ...:         references staff
    ...:             on update cascade
    ...: )
    ...: '''
    ...: parse_one(s, read="sqlite")
    ...:
---------------------------------------------------------------------------
ParseError                                Traceback (most recent call last)
Cell In[17], line 13
      1 from sqlglot import parse_one
      2 s = '''
      3 CREATE TABLE "store"
      4 (
   (...)
     11 )
     12 '''
---> 13 parse_one(s, read="sqlite")

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/__init__.py:139, in parse_one(sql, read, dialect, into, **opts)
    137     result = dialect.parse_into(into, sql, **opts)
    138 else:
--> 139     result = dialect.parse(sql, **opts)
    141 for expression in result:
    142     if not expression:

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/dialects/dialect.py:920, in Dialect.parse(self, sql, **opts)
    919 def parse(self, sql: str, **opts) -> t.List[t.Optional[exp.Expression]]:
--> 920     return self.parser(**opts).parse(self.tokenize(sql), sql)

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1416, in Parser.parse(self, raw_tokens, sql)
   1402 def parse(
   1403     self, raw_tokens: t.List[Token], sql: t.Optional[str] = None
   1404 ) -> t.List[t.Optional[exp.Expression]]:
   1405     """
   1406     Parses a list of tokens and returns a list of syntax trees, one tree
   1407     per parsed SQL statement.
   (...)
   1414         The list of the produced syntax trees.
   1415     """
-> 1416     return self._parse(
   1417         parse_method=self.__class__._parse_statement, raw_tokens=raw_tokens, sql=sql
   1418     )

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1485, in Parser._parse(self, parse_method, raw_tokens, sql)
   1482 self._tokens = tokens
   1483 self._advance()
-> 1485 expressions.append(parse_method(self))
   1487 if self._index < len(self._tokens):
   1488     self.raise_error("Invalid expression / Unexpected token")

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1717, in Parser._parse_statement(self)
   1714     return None
   1716 if self._match_set(self.STATEMENT_PARSERS):
-> 1717     return self.STATEMENT_PARSERS[self._prev.token_type](self)
   1719 if self._match_set(self.dialect.tokenizer.COMMANDS):
   1720     return self._parse_command()

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:785, in Parser.<lambda>(self)
    711 COLUMN_OPERATORS = {
    712     TokenType.DOT: None,
    713     TokenType.DCOLON: lambda self, this, to: self.expression(
   (...)
    744     ),
    745 }
    747 EXPRESSION_PARSERS = {
    748     exp.Cluster: lambda self: self._parse_sort(exp.Cluster, TokenType.CLUSTER_BY),
    749     exp.Column: lambda self: self._parse_column(),
   (...)
    775     "JOIN_TYPE": lambda self: self._parse_join_parts(),
    776 }
    778 STATEMENT_PARSERS = {
    779     TokenType.ALTER: lambda self: self._parse_alter(),
    780     TokenType.BEGIN: lambda self: self._parse_transaction(),
    781     TokenType.CACHE: lambda self: self._parse_cache(),
    782     TokenType.COMMENT: lambda self: self._parse_comment(),
    783     TokenType.COMMIT: lambda self: self._parse_commit_or_rollback(),
    784     TokenType.COPY: lambda self: self._parse_copy(),
--> 785     TokenType.CREATE: lambda self: self._parse_create(),
    786     TokenType.DELETE: lambda self: self._parse_delete(),
    787     TokenType.DESC: lambda self: self._parse_describe(),
    788     TokenType.DESCRIBE: lambda self: self._parse_describe(),
    789     TokenType.DROP: lambda self: self._parse_drop(),
    790     TokenType.GRANT: lambda self: self._parse_grant(),
    791     TokenType.INSERT: lambda self: self._parse_insert(),
    792     TokenType.KILL: lambda self: self._parse_kill(),
    793     TokenType.LOAD: lambda self: self._parse_load(),
    794     TokenType.MERGE: lambda self: self._parse_merge(),
    795     TokenType.PIVOT: lambda self: self._parse_simplified_pivot(),
    796     TokenType.PRAGMA: lambda self: self.expression(exp.Pragma, this=self._parse_expression()),
    797     TokenType.REFRESH: lambda self: self._parse_refresh(),
    798     TokenType.ROLLBACK: lambda self: self._parse_commit_or_rollback(),
    799     TokenType.SET: lambda self: self._parse_set(),
    800     TokenType.TRUNCATE: lambda self: self._parse_truncate_table(),
    801     TokenType.UNCACHE: lambda self: self._parse_uncache(),
    802     TokenType.UPDATE: lambda self: self._parse_update(),
    803     TokenType.USE: lambda self: self.expression(
    804         exp.Use,
    805         kind=self._parse_var_from_options(self.USABLES, raise_unmatched=False),
    806         this=self._parse_table(schema=False),
    807     ),
    808     TokenType.SEMICOLON: lambda self: self.expression(exp.Semicolon),
    809 }
    811 UNARY_PARSERS = {
    812     TokenType.PLUS: lambda self: self._parse_unary(),  # Unary + is handled as a no-op
    813     TokenType.NOT: lambda self: self.expression(exp.Not, this=self._parse_equality()),
   (...)
    817     TokenType.DPIPE_SLASH: lambda self: self.expression(exp.Cbrt, this=self._parse_unary()),
    818 }
    820 STRING_PARSERS = {
    821     TokenType.HEREDOC_STRING: lambda self, token: self.expression(
    822         exp.RawString, this=token.text
   (...)
    835     ),
    836 }

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1872, in Parser._parse_create(self)
   1869 self._match(TokenType.COMMA)
   1870 extend_props(self._parse_properties(before=True))
-> 1872 this = self._parse_schema(this=table_parts)
   1874 # exp.Properties.Location.POST_SCHEMA and POST_WITH
   1875 extend_props(self._parse_properties())

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:5426, in Parser._parse_schema(self, this)
   5424     return this
   5425 args = self._parse_csv(lambda: self._parse_constraint() or self._parse_field_def())
-> 5426 self._match_r_paren()
   5427 return self.expression(exp.Schema, this=this, expressions=args)

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:7222, in Parser._match_r_paren(self, expression)
   7220 def _match_r_paren(self, expression: t.Optional[exp.Expression] = None) -> None:
   7221     if not self._match(TokenType.R_PAREN, expression=expression):
-> 7222         self.raise_error("Expecting )")

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1529, in Parser.raise_error(self, message, token)
   1517 error = ParseError.new(
   1518     f"{message}. Line {token.line}, Col: {token.col}.\n"
   1519     f"  {start_context}\033[4m{highlight}\033[0m{end_context}",
   (...)
   1525     end_context=end_context,
   1526 )
   1528 if self.error_level == ErrorLevel.IMMEDIATE:
-> 1529     raise error
   1531 self.errors.append(error)

ParseError: Expecting ). Line 8, Col: 17.

    manager_staff_id INTEGER                            not null
        unique
        references staff
            on update cascade
)
```

Discussion:
@tshu-w: below are sqls from https://bird-bench.github.io but got parse error with sqlglot
```python
sqls = '''CREATE TABLE "XmlData"
(
    Xml TEXT,
    PostId INTEGER,
    QuestionId INTEGER,
    AnswerId INTEGER,
    parent ID INTEGER,
    foreign key (PostId) references Post(Id)
        on update cascade on delete cascade
)

CREATE TABLE "user"
(
    user_id         TEXT not null
        primary key,
    name            TEXT,
    review_count    INTEGER,
    helpfulness     INTEGER,
    thank you_count INTEGER,
    star_average    REAL,
    fans             INTEGER
)

CREATE TABLE "Player_Statistics"
(
    id             INTEGER not null
            primary key,
    stat_date      TEXT,
    league_id      INTEGER,
    tour_id        INTEGER,
    round          TEXT,
    match_day       INTEGER,
    season         TEXT,
    playoff        TEXT,
    home Away      TEXT,
    team_id        INTEGER,
    team_name      TEXT,
    opp_id        INTEGER,
    opp_name      TEXT,
    result         TEXT,
    minutes        INTEGER,
    shoots_total   INTEGER,
    shoots_on      INTEGER,
    opp_shoots_total INTEGER,
    opp_shoots_on  INTEGER,
    possession    INTEGER,
    off_wins      TEXT,
    off_losses     TEXT,
    foul_committed INTEGER,
    foul_received  INTEGER,
    player_id      INTEGER,
    player_name    TEXT,
    goals         INTEGER,
    assists        INTEGER,
    rebounds       INTEGER,
    turnovers     INTEGER,
    personal_fouls INTEGER,
    tech_fouls    INTEGER,
   doubles_fouls  INTEGER,
     power_play   TEXT,
     min_weather_temperature TEXT,
     max_weather_temperature TEXT,
     weather      TEXT,
     surface      TEXT,
     light        TEXT
)

CREATE TABLE "Team_Avg"
(
    id                 INTEGER      not null
        primary key,
    year               INTEGER      not null,
    team_id            INTEGER      not null,
    team_name          TEXT         not null,
    team_nametext      TEXT         not null,
    conf_id           INTEGER      not null,
    conf_name         INTEGER      not null,
    conf_name_text    TEXT         not null,
    div_id            INTEGER      not null,
    div_name          INTEGER      not null,
    div_name_text     TEXT         not null,
    league_id         INTEGER      not null,
    league_name       TEXT         not null,
    league_name_text  TEXT         not null,
    avg_g              TEXT        null,
    avg ga            TEXT        null,
    avg_xpm           TEXT        null,
    avg_xpa           TEXT        null,
    avg_ypr_l         TEXT        null,
    avg_ypr_r         TEXT        null,
    avg_yrd_l         TEXT        null,
    avg_yrd_r         TEXT        null,
    avg_yrt_l         TEXT        null,
    avg_yrt_r         TEXT        null,
    avg_fgm           TEXT        null,
    avg_fgpm         TEXT        null,
    avg_fgpa         TEXT        null,
    avg_ftpm          TEXT        null,
    avg_ftpp         TEXT        null,
    avg_ortg         TEXT        null,
    avg_drtg         TEXT        null,
    avgasts          TEXT        null,
    avg_stl           TEXT        null,
    avg_blk           TEXT        null,
    avg_tov           TEXT        null,
    avg_oreb         TEXT        null,
    avg_dreb         TEXT        null,
    avg_reb          TEXT        null,
    avg_pf            TEXT        null,
    avg_pts100       TEXT        null,
    avg_mpm          TEXT        null,
    avg_tm3pm        TEXT        null,
    avg_tm3pa        TEXT        null,
    avg_tm3pp        TEXT        null,
    avg_tm2pm        TEXT        null,
    avg_tm2pa        TEXT        null,
    avg_tm2pp        TEXT        null,
    avg_fta           TEXT        null
)

CREATE TABLE "store"
(
    store_id         INTEGER
        primary key autoincrement,
    manager_staff_id INTEGER                            not null
        unique
        references staff
            on update cascade,
    address_id       INTEGER                            not null
        references address
            on update cascade,
    last_update      DATETIME default CURRENT_TIMESTAMP not null
)

CREATE TABLE "scryfall_related"
(
    source Scryfall_id TEXT,
    card   mtg_id      TEXT,
    type_  TEXT,
    Scryfall_id_ TEXT,
    constraint scryfall_related_pk
        primary key (source, card),
    constraint scryfall_related_fk
        foreign key (card) references cards (id)
            on update cascade on delete cascade
)

CREATE TABLE "users"
(
    id                INTEGER
        primary key AUTOINCREMENT,
    email              TEXT
            references "customer"
            on update cascade on delete cascade unique ,
    user_date_registered DATE,
    user_login         TEXT,
    user Nicename      TEXT,
    user_displayname   TEXT
)'''.split("\n\n")
```@VaggelisD: Hey @tshu-w, thanks for reporting these cases.

It's not clear to us what's the purpose of having space separated names, e.g:

```
CREATE TABLE "users"
(
    id                INTEGER
        primary key AUTOINCREMENT,
    email              TEXT
            references "customer"
            on update cascade on delete cascade unique ,
    user_date_registered DATE,
    user_login         TEXT,
    user Nicename      TEXT,
    user_displayname   TEXT
)
```

The reason this leads to a parse error in sqlglot is because of `user Nicename`. Is this intentional? Does it serve any purpose in SQLite?@tshu-w: > The reason this leads to a parse error in sqlglot is because of `user Nicename`. Is this intentional? Does it serve any purpose in SQLite?

I'm not sure, the statement is get by:
```python
cursor.execute(
    f"SELECT sql FROM sqlite_master WHERE type='table' AND name='{table_name}';"
)
create_prompt = cursor.fetchone()[0]
```
 I think it might be an issue with the database provided by the benchmark. Let's ignore them.

Solution patch:
diff --git a/sqlglot/dialects/sqlite.py b/sqlglot/dialects/sqlite.py
index d71266349d..026988091d 100644
--- a/sqlglot/dialects/sqlite.py
+++ b/sqlglot/dialects/sqlite.py
@@ -120,6 +120,14 @@ class Parser(parser.Parser):
         }
         STRING_ALIASES = True
 
+        def _parse_unique(self) -> exp.UniqueColumnConstraint:
+            # Do not consume more tokens if UNIQUE is used as a standalone constraint, e.g:
+            # CREATE TABLE foo (bar TEXT UNIQUE REFERENCES baz ...)
+            if self._curr.text.upper() in self.CONSTRAINT_PARSERS:
+                return self.expression(exp.UniqueColumnConstraint)
+
+            return super()._parse_unique()
+
     class Generator(generator.Generator):
         JOIN_HINTS = False
         TABLE_HINTS = False


Test patch:
diff --git a/tests/dialects/test_sqlite.py b/tests/dialects/test_sqlite.py
index 230c0e8fea..e37cdc8ec6 100644
--- a/tests/dialects/test_sqlite.py
+++ b/tests/dialects/test_sqlite.py
@@ -222,3 +222,7 @@ def test_ddl(self):
                 "mysql": "CREATE TABLE `x` (`Name` VARCHAR(200) NOT NULL)",
             },
         )
+
+        self.validate_identity(
+            "CREATE TABLE store (store_id INTEGER PRIMARY KEY AUTOINCREMENT, mgr_id INTEGER NOT NULL UNIQUE REFERENCES staff ON UPDATE CASCADE)"
+        )