Repository: tobymao/sqlglot

Issue:
Parsing failure on Databricks alternative foreign key constraint syntax
**Context**
Databricks allows for foreign key constraints to be written in the same statement as the column definition. sqlglot expects there to be a column specified in the reference - but databricks allows this column to be omitted when referencing the same column name on the foreign table. 

 
**Fully reproducible code snippet**
```
# /// script
# requires-python = "==3.13.3"
# dependencies = [
#     "sqlglot",
# ]
#
# [tool.uv.sources]
# sqlglot = { git = "https://github.com/tobymao/sqlglot", branch = "main" }
# ///
import sqlglot

sqlglot.parse_one(
    """
CREATE OR REPLACE TABLE example.bug_table (
  PK BIGINT NOT NULL PRIMARY KEY,
  FK BIGINT NOT NULL
    CONSTRAINT BUG_TABLE_FOREIGN_TABLE_FK
    FOREIGN KEY REFERENCES example.foreign_table
)
""",
    dialect="databricks",
)
```
**Stack Trace**
```
PS C:\Users\example> uv run .\demo.py                                                   
      Built sqlglot @ git+https://github.com/tobymao/sqlglot@411e1304905686388d0e7c466937076f100efe2d
Uninstalled 1 package in 65ms                                                                                                                   
Installed 1 package in 94ms
Traceback (most recent call last):
  File "C:\Users\example\demo.py", line 12, in <module>
    sqlglot.parse_one(
    ~~~~~~~~~~~~~~~~~^
        """
        ^^^
    ...<7 lines>...
        dialect="databricks",
        ^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\__init__.py", line 139, in
 parse_one
    result = dialect.parse(sql, **opts)
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\dialects\dialect.py", line
 991, in parse
    return self.parser(**opts).parse(self.tokenize(sql), sql)
           ~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 1552, in parse
    return self._parse(
           ~~~~~~~~~~~^
        parse_method=self.__class__._parse_statement, raw_tokens=raw_tokens, sql=sql
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 1621, in _parse
    expressions.append(parse_method(self))
                       ~~~~~~~~~~~~^^^^^^
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 1854, in _parse_statement
    stmt = self.STATEMENT_PARSERS[self._prev.token_type](self)
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 831, in <lambda>
    TokenType.CREATE: lambda self: self._parse_create(),
                                   ~~~~~~~~~~~~~~~~~~^^
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 2013, in _parse_create
    this = self._parse_schema(this=table_parts)
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 5815, in _parse_schema
    args = self._parse_csv(lambda: self._parse_constraint() or self._parse_field_def())
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 7088, in _parse_csv
    parse_result = parse_method()
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 5815, in <lambda>
    args = self._parse_csv(lambda: self._parse_constraint() or self._parse_field_def())
                                                               ~~~~~~~~~~~~~~~~~~~~~^^
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 5820, in _parse_field_def
    return self._parse_column_def(self._parse_field(any_token=True))
           ~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 5866, in _parse_column_def
    constraint = self._parse_column_constraint()
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 5994, in _parse_column_constraint
    kind=self.CONSTRAINT_PARSERS[self._prev.text.upper()](self),
         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 1080, in <lambda>
    "FOREIGN KEY": lambda self: self._parse_foreign_key(),
                                ~~~~~~~~~~~~~~~~~~~~~~~^^
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 6091, in _parse_foreign_key
    expressions = self._parse_wrapped_id_vars()
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 7110, in _parse_wrapped_id_vars
    return self._parse_wrapped_csv(self._parse_id_var, optional=optional)
           ~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 7115, in _parse_wrapped_csv
    return self._parse_wrapped(
           ~~~~~~~~~~~~~~~~~~~^
        lambda: self._parse_csv(parse_method, sep=sep), optional=optional
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 7122, in _parse_wrapped
    self.raise_error("Expecting (")
    ~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^
  File "C:\Users\example\.venv\Lib\site-packages\sqlglot\parser.py", line 1665, in raise_error
    raise error
sqlglot.errors.ParseError: Expecting (. Line 6, Col: 23.
  OT NULL PRIMARY KEY,
  FK BIGINT NOT NULL
    CONSTRAINT BUG_TABLE_FOREIGN_TABLE_FK
    FOREIGN KEY REFERENCES example.foreign_table
)
```
**Official Documentation**
See azure databricks docs [here](https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/sql-ref-syntax-ddl-create-table-constraint).

```
column_constraint
  { [ CONSTRAINT name ]
    { PRIMARY KEY [ constraint_option ] [...] |
      { [ FOREIGN KEY ]
        REFERENCES parent_table [ ( parent_column [, ...] ) ]
        [ foreign_key_option | constraint_option ] [...]
      }
    }
  }
```

Hints from issue comments:
@Steven-Wright: Thanks for getting around to this - much appreciated!

Solution patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 73bd0ed26d..da7f43dc59 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -614,6 +614,7 @@ class Generator(generator.Generator):
         MULTI_ARG_DISTINCT = False
         CAN_IMPLEMENT_ARRAY_ANY = True
         SUPPORTS_TO_NUMBER = False
+        SUPPORTS_WINDOW_EXCLUDE = True
         COPY_HAS_INTO_KEYWORD = False
         STAR_EXCEPT = "EXCLUDE"
         PAD_FILL_PATTERN_IS_REQUIRED = True
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 185907b2c8..1608cf35ba 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -251,6 +251,7 @@ class Generator(generator.Generator):
         LAST_DAY_SUPPORTS_DATE_PART = False
         SUPPORTS_SELECT_INTO = True
         TZ_TO_WITH_TIME_ZONE = True
+        SUPPORTS_WINDOW_EXCLUDE = True
         QUERY_HINT_SEP = " "
 
         TYPE_MAPPING = {
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 181ca11082..c9e0402d15 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -519,6 +519,7 @@ class Generator(generator.Generator):
         LIKE_PROPERTY_INSIDE_SCHEMA = True
         MULTI_ARG_DISTINCT = False
         CAN_IMPLEMENT_ARRAY_ANY = True
+        SUPPORTS_WINDOW_EXCLUDE = True
         COPY_HAS_INTO_KEYWORD = False
         ARRAY_CONCAT_IS_VAR_LEN = False
         SUPPORTS_MEDIAN = False
diff --git a/sqlglot/dialects/sqlite.py b/sqlglot/dialects/sqlite.py
index 659ba64492..7e12215ce5 100644
--- a/sqlglot/dialects/sqlite.py
+++ b/sqlglot/dialects/sqlite.py
@@ -124,6 +124,7 @@ class Generator(generator.Generator):
         SUPPORTS_CREATE_TABLE_LIKE = False
         SUPPORTS_TABLE_ALIAS_COLUMNS = False
         SUPPORTS_TO_NUMBER = False
+        SUPPORTS_WINDOW_EXCLUDE = True
         EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
         SUPPORTS_MEDIAN = False
         JSON_KEY_VALUE_PAIR_SEP = ","
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 90b2ae5bf8..5ade6534f6 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -4423,6 +4423,7 @@ class WindowSpec(Expression):
         "start_side": False,
         "end": False,
         "end_side": False,
+        "exclude": False,
     }
 
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index b4c280a757..38f58235ba 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -404,6 +404,9 @@ class Generator(metaclass=_Generator):
     # Whether the function TO_NUMBER is supported
     SUPPORTS_TO_NUMBER = True
 
+    # Whether EXCLUDE in window specification is supported
+    SUPPORTS_WINDOW_EXCLUDE = False
+
     # Whether or not set op modifiers apply to the outer set op or select.
     # SELECT * FROM x UNION SELECT * FROM y LIMIT 1
     # True means limit 1 happens after the set op, False means it it happens on y.
@@ -2806,7 +2809,17 @@ def windowspec_sql(self, expression: exp.WindowSpec) -> str:
             csv(self.sql(expression, "end"), self.sql(expression, "end_side"), sep=" ")
             or "CURRENT ROW"
         )
-        return f"{kind} BETWEEN {start} AND {end}"
+
+        window_spec = f"{kind} BETWEEN {start} AND {end}"
+
+        exclude = self.sql(expression, "exclude")
+        if exclude:
+            if self.SUPPORTS_WINDOW_EXCLUDE:
+                window_spec += f" EXCLUDE {exclude}"
+            else:
+                self.unsupported("EXCLUDE clause is not supported in the WINDOW clause")
+
+        return window_spec
 
     def withingroup_sql(self, expression: exp.WithinGroup) -> str:
         this = self.sql(expression, "this")
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index ef4f67b4f1..a2c822bc00 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -1345,6 +1345,12 @@ def _parse_partitioned_by_bucket_or_truncate(self) -> exp.Expression:
         **dict.fromkeys(("DEFERRABLE", "NORELY", "RELY"), tuple()),
     }
 
+    WINDOW_EXCLUDE_OPTIONS: OPTIONS_TYPE = {
+        "NO": ("OTHERS",),
+        "CURRENT": ("ROW",),
+        **dict.fromkeys(("GROUP", "TIES"), tuple()),
+    }
+
     INSERT_ALTERNATIVES = {"ABORT", "FAIL", "IGNORE", "REPLACE", "ROLLBACK"}
 
     CLONE_KEYWORDS = {"CLONE", "COPY"}
@@ -6088,7 +6094,11 @@ def _parse_references(self, match: bool = True) -> t.Optional[exp.Reference]:
         return self.expression(exp.Reference, this=this, expressions=expressions, options=options)
 
     def _parse_foreign_key(self) -> exp.ForeignKey:
-        expressions = self._parse_wrapped_id_vars()
+        expressions = (
+            self._parse_wrapped_id_vars()
+            if not self._match(TokenType.REFERENCES, advance=False)
+            else None
+        )
         reference = self._parse_references()
         on_options = {}
 
@@ -6887,6 +6897,11 @@ def _parse_window(
             start = self._parse_window_spec()
             self._match(TokenType.AND)
             end = self._parse_window_spec()
+            exclude = (
+                self._parse_var_from_options(self.WINDOW_EXCLUDE_OPTIONS)
+                if self._match_text_seq("EXCLUDE")
+                else None
+            )
 
             spec = self.expression(
                 exp.WindowSpec,
@@ -6895,6 +6910,7 @@ def _parse_window(
                 start_side=start["side"],
                 end=end["value"],
                 end_side=end["side"],
+                exclude=exclude,
             )
         else:
             spec = None


Test patch:
diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py
index 723767c0cd..52e3e8903d 100644
--- a/tests/dialects/test_databricks.py
+++ b/tests/dialects/test_databricks.py
@@ -162,6 +162,12 @@ def test_databricks(self):
             },
         )
 
+        for option in ("", " (foo)", " MATCH FULL", " NOT ENFORCED"):
+            with self.subTest(f"Databricks foreign key REFERENCES option: {option}."):
+                self.validate_identity(
+                    f"CREATE TABLE t1 (foo BIGINT NOT NULL CONSTRAINT foo_c FOREIGN KEY REFERENCES t2{option})"
+                )
+
     # https://docs.databricks.com/sql/language-manual/functions/colonsign.html
     def test_json(self):
         self.validate_identity("SELECT c1:price, c1:price.foo, c1:price.bar[1]")
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index abf9ccb746..65b2c580de 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -2709,6 +2709,35 @@ def test_qualify(self):
             },
         )
 
+    def test_window_exclude(self):
+        for option in ("CURRENT ROW", "TIES", "GROUP"):
+            self.validate_all(
+                f"SELECT SUM(X) OVER (PARTITION BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE {option})",
+                write={
+                    "duckdb": f"SELECT SUM(X) OVER (PARTITION BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE {option})",
+                    "postgres": f"SELECT SUM(X) OVER (PARTITION BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE {option})",
+                    "sqlite": f"SELECT SUM(X) OVER (PARTITION BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE {option})",
+                    "oracle": f"SELECT SUM(X) OVER (PARTITION BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE {option})",
+                },
+            )
+
+        # EXCLUDE NO OTHERS is the default behaviour
+        self.validate_all(
+            "SELECT SUM(X) OVER (PARTITION BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)",
+            read={
+                "duckdb": "SELECT SUM(X) OVER (PARTITION BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS)",
+                "postgres": "SELECT SUM(X) OVER (PARTITION BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS)",
+                "sqlite": "SELECT SUM(X) OVER (PARTITION BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS)",
+                "oracle": "SELECT SUM(X) OVER (PARTITION BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS)",
+            },
+            write={
+                "duckdb": "SELECT SUM(X) OVER (PARTITION BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)",
+                "postgres": "SELECT SUM(X) OVER (PARTITION BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)",
+                "sqlite": "SELECT SUM(X) OVER (PARTITION BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)",
+                "oracle": "SELECT SUM(X) OVER (PARTITION BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)",
+            },
+        )
+
     def test_nested_ctes(self):
         self.validate_all(
             "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq",