Repository: cloudspannerecosystem/memefish
Pull request #73 (2023-09-02 02:37:03)

Add support for foreign key actions
This PR adds support for [foreign key actions](https://cloud.google.com/spanner/docs/foreign-keys/overview?hl=en#how-to-define-foreign-key-action)

toga4: @makenowjust Thank you for review. Now fixed.

Patch:
diff --git a/ast/ast.go b/ast/ast.go
index c8e007f..2ac1148 100644
--- a/ast/ast.go
+++ b/ast/ast.go
@@ -1490,17 +1490,19 @@ type TableConstraint struct {
 
 // ForeignKey is foreign key specifier in CREATE TABLE and ALTER TABLE.
 //
-//	FOREIGN KEY ({{.ColumnNames | sqlJoin ","}}) REFERENCES {{.ReferenceTable}} ({{.ReferenceColumns | sqlJoin ","}})
+//	FOREIGN KEY ({{.ColumnNames | sqlJoin ","}}) REFERENCES {{.ReferenceTable}} ({{.ReferenceColumns | sqlJoin ","}}) {{.OnDelete}}
 type ForeignKey struct {
 	// pos = Foreign
-	// end = Rparen + 1
+	// end = OnDeleteEnd || Rparen + 1
 
-	Foreign token.Pos // position of "FOREIGN" keyword
-	Rparen  token.Pos // position of ")" after reference columns
+	Foreign     token.Pos // position of "FOREIGN" keyword
+	Rparen      token.Pos // position of ")" after reference columns
+	OnDeleteEnd token.Pos // end position of ON DELETE clause
 
 	Columns          []*Ident
 	ReferenceTable   *Ident
-	ReferenceColumns []*Ident // len(ReferenceColumns) > 0
+	ReferenceColumns []*Ident       // len(ReferenceColumns) > 0
+	OnDelete         OnDeleteAction // optional
 }
 
 // Check is check constraint in CREATE TABLE and ALTER TABLE.
diff --git a/ast/pos.go b/ast/pos.go
index cb95ecb..5103424 100644
--- a/ast/pos.go
+++ b/ast/pos.go
@@ -552,7 +552,12 @@ func (c *TableConstraint) End() token.Pos { return c.Constraint.End() }
 func (f *ForeignKey) Pos() token.Pos {
 	return f.Foreign
 }
-func (f *ForeignKey) End() token.Pos { return f.Rparen + 1 }
+func (f *ForeignKey) End() token.Pos {
+	if !f.OnDeleteEnd.Invalid() {
+		return f.OnDeleteEnd
+	}
+	return f.Rparen + 1
+}
 
 func (c *Check) Pos() token.Pos { return c.Check }
 func (c *Check) End() token.Pos { return c.Rparen + 1 }
diff --git a/ast/sql.go b/ast/sql.go
index a2ad597..690cf00 100644
--- a/ast/sql.go
+++ b/ast/sql.go
@@ -819,6 +819,9 @@ func (f *ForeignKey) SQL() string {
 		sql += k.SQL()
 	}
 	sql += ")"
+	if f.OnDelete != "" {
+		sql += " " + string(f.OnDelete)
+	}
 	return sql
 }
 
diff --git a/parser.go b/parser.go
index c889328..ffa04a7 100644
--- a/parser.go
+++ b/parser.go
@@ -2226,12 +2226,16 @@ func (p *Parser) parseForeignKey() *ast.ForeignKey {
 	}
 	rparen := p.expect(")").End
 
+	onDelete, onDeleteEnd := p.tryParseOnDeleteAction()
+
 	return &ast.ForeignKey{
 		Foreign:          pos,
 		Rparen:           rparen,
+		OnDeleteEnd:      onDeleteEnd,
 		Columns:          columns,
 		ReferenceTable:   refTable,
 		ReferenceColumns: refColumns,
+		OnDelete:         onDelete,
 	}
 }
 


Test patch:
diff --git a/testdata/input/ddl/alter_table_add_constraint_foreign_key_on_delete_cascade.sql b/testdata/input/ddl/alter_table_add_constraint_foreign_key_on_delete_cascade.sql
new file mode 100644
index 0000000..963a778
--- /dev/null
+++ b/testdata/input/ddl/alter_table_add_constraint_foreign_key_on_delete_cascade.sql
@@ -0,0 +1,1 @@
+alter table foo add constraint fkname foreign key (foo, bar) references t2 (t2key1, t2key2) on delete cascade
diff --git a/testdata/input/ddl/alter_table_add_constraint_foreign_key_on_delete_no_action.sql b/testdata/input/ddl/alter_table_add_constraint_foreign_key_on_delete_no_action.sql
new file mode 100644
index 0000000..90d58a4
--- /dev/null
+++ b/testdata/input/ddl/alter_table_add_constraint_foreign_key_on_delete_no_action.sql
@@ -0,0 +1,1 @@
+alter table foo add constraint fkname foreign key (foo, bar) references t2 (t2key1, t2key2) on delete no action
diff --git a/testdata/input/ddl/create_table.sql b/testdata/input/ddl/create_table.sql
index 46e8002..4bae8db 100644
--- a/testdata/input/ddl/create_table.sql
+++ b/testdata/input/ddl/create_table.sql
@@ -4,6 +4,8 @@ create table foo (
   baz string(255) not null options(allow_commit_timestamp = null),
   qux string(255) not null as (concat(baz, "a")) stored,
   foreign key (foo) references t2 (t2key1),
+  foreign key (bar) references t2 (t2key2) on delete cascade,
+  foreign key (baz) references t2 (t2key3) on delete no action,
   constraint fkname foreign key (foo, bar) references t2 (t2key1, t2key2),
   check (foo > 0),
   constraint cname check (bar > 0),
diff --git a/testdata/result/ddl/alter_table_add_constraint_foreign_key.sql.txt b/testdata/result/ddl/alter_table_add_constraint_foreign_key.sql.txt
index 6491304..2f63b9a 100644
--- a/testdata/result/ddl/alter_table_add_constraint_foreign_key.sql.txt
+++ b/testdata/result/ddl/alter_table_add_constraint_foreign_key.sql.txt
@@ -19,9 +19,10 @@ alter table foo add constraint fkname foreign key (foo, bar) references t2 (t2ke
         Name:    "fkname",
       },
       Constraint: &ast.ForeignKey{
-        Foreign: 38,
-        Rparen:  91,
-        Columns: []*ast.Ident{
+        Foreign:     38,
+        Rparen:      91,
+        OnDeleteEnd: -1,
+        Columns:     []*ast.Ident{
           &ast.Ident{
             NamePos: 51,
             NameEnd: 54,
@@ -50,6 +51,7 @@ alter table foo add constraint fkname foreign key (foo, bar) references t2 (t2ke
             Name:    "t2key2",
           },
         },
+        OnDelete: "",
       },
     },
   },
diff --git a/testdata/result/ddl/alter_table_add_constraint_foreign_key_on_delete_cascade.sql.txt b/testdata/result/ddl/alter_table_add_constraint_foreign_key_on_delete_cascade.sql.txt
new file mode 100644
index 0000000..1cfcc42
--- /dev/null
+++ b/testdata/result/ddl/alter_table_add_constraint_foreign_key_on_delete_cascade.sql.txt
@@ -0,0 +1,61 @@
+--- alter_table_add_constraint_foreign_key_on_delete_cascade.sql
+alter table foo add constraint fkname foreign key (foo, bar) references t2 (t2key1, t2key2) on delete cascade
+
+--- AST
+&ast.AlterTable{
+  Alter: 0,
+  Name:  &ast.Ident{
+    NamePos: 12,
+    NameEnd: 15,
+    Name:    "foo",
+  },
+  TableAlternation: &ast.AddTableConstraint{
+    Add:             16,
+    TableConstraint: &ast.TableConstraint{
+      ConstraintPos: 20,
+      Name:          &ast.Ident{
+        NamePos: 31,
+        NameEnd: 37,
+        Name:    "fkname",
+      },
+      Constraint: &ast.ForeignKey{
+        Foreign:     38,
+        Rparen:      91,
+        OnDeleteEnd: 109,
+        Columns:     []*ast.Ident{
+          &ast.Ident{
+            NamePos: 51,
+            NameEnd: 54,
+            Name:    "foo",
+          },
+          &ast.Ident{
+            NamePos: 56,
+            NameEnd: 59,
+            Name:    "bar",
+          },
+        },
+        ReferenceTable: &ast.Ident{
+          NamePos: 72,
+          NameEnd: 74,
+          Name:    "t2",
+        },
+        ReferenceColumns: []*ast.Ident{
+          &ast.Ident{
+            NamePos: 76,
+            NameEnd: 82,
+            Name:    "t2key1",
+          },
+          &ast.Ident{
+            NamePos: 84,
+            NameEnd: 90,
+            Name:    "t2key2",
+          },
+        },
+        OnDelete: "ON DELETE CASCADE",
+      },
+    },
+  },
+}
+
+--- SQL
+ALTER TABLE foo ADD CONSTRAINT fkname FOREIGN KEY (foo, bar) REFERENCES t2 (t2key1, t2key2) ON DELETE CASCADE
diff --git a/testdata/result/ddl/alter_table_add_constraint_foreign_key_on_delete_no_action.sql.txt b/testdata/result/ddl/alter_table_add_constraint_foreign_key_on_delete_no_action.sql.txt
new file mode 100644
index 0000000..a9fde93
--- /dev/null
+++ b/testdata/result/ddl/alter_table_add_constraint_foreign_key_on_delete_no_action.sql.txt
@@ -0,0 +1,61 @@
+--- alter_table_add_constraint_foreign_key_on_delete_no_action.sql
+alter table foo add constraint fkname foreign key (foo, bar) references t2 (t2key1, t2key2) on delete no action
+
+--- AST
+&ast.AlterTable{
+  Alter: 0,
+  Name:  &ast.Ident{
+    NamePos: 12,
+    NameEnd: 15,
+    Name:    "foo",
+  },
+  TableAlternation: &ast.AddTableConstraint{
+    Add:             16,
+    TableConstraint: &ast.TableConstraint{
+      ConstraintPos: 20,
+      Name:          &ast.Ident{
+        NamePos: 31,
+        NameEnd: 37,
+        Name:    "fkname",
+      },
+      Constraint: &ast.ForeignKey{
+        Foreign:     38,
+        Rparen:      91,
+        OnDeleteEnd: 111,
+        Columns:     []*ast.Ident{
+          &ast.Ident{
+            NamePos: 51,
+            NameEnd: 54,
+            Name:    "foo",
+          },
+          &ast.Ident{
+            NamePos: 56,
+            NameEnd: 59,
+            Name:    "bar",
+          },
+        },
+        ReferenceTable: &ast.Ident{
+          NamePos: 72,
+          NameEnd: 74,
+          Name:    "t2",
+        },
+        ReferenceColumns: []*ast.Ident{
+          &ast.Ident{
+            NamePos: 76,
+            NameEnd: 82,
+            Name:    "t2key1",
+          },
+          &ast.Ident{
+            NamePos: 84,
+            NameEnd: 90,
+            Name:    "t2key2",
+          },
+        },
+        OnDelete: "ON DELETE NO ACTION",
+      },
+    },
+  },
+}
+
+--- SQL
+ALTER TABLE foo ADD CONSTRAINT fkname FOREIGN KEY (foo, bar) REFERENCES t2 (t2key1, t2key2) ON DELETE NO ACTION
diff --git a/testdata/result/ddl/alter_table_add_foreign_key.sql.txt b/testdata/result/ddl/alter_table_add_foreign_key.sql.txt
index ef7e54c..7bf717f 100644
--- a/testdata/result/ddl/alter_table_add_foreign_key.sql.txt
+++ b/testdata/result/ddl/alter_table_add_foreign_key.sql.txt
@@ -15,9 +15,10 @@ alter table foo add foreign key (bar) references t2 (t2key1)
       ConstraintPos: 0,
       Name:          (*ast.Ident)(nil),
       Constraint:    &ast.ForeignKey{
-        Foreign: 20,
-        Rparen:  60,
-        Columns: []*ast.Ident{
+        Foreign:     20,
+        Rparen:      60,
+        OnDeleteEnd: -1,
+        Columns:     []*ast.Ident{
           &ast.Ident{
             NamePos: 33,
             NameEnd: 36,
@@ -36,6 +37,7 @@ alter table foo add foreign key (bar) references t2 (t2key1)
             Name:    "t2key1",
           },
         },
+        OnDelete: "",
       },
     },
   },
diff --git a/testdata/result/ddl/create_table.sql.txt b/testdata/result/ddl/create_table.sql.txt
index 08784aa..b0640aa 100644
--- a/testdata/result/ddl/create_table.sql.txt
+++ b/testdata/result/ddl/create_table.sql.txt
@@ -5,6 +5,8 @@ create table foo (
   baz string(255) not null options(allow_commit_timestamp = null),
   qux string(255) not null as (concat(baz, "a")) stored,
   foreign key (foo) references t2 (t2key1),
+  foreign key (bar) references t2 (t2key2) on delete cascade,
+  foreign key (baz) references t2 (t2key3) on delete no action,
   constraint fkname foreign key (foo, bar) references t2 (t2key1, t2key2),
   check (foo > 0),
   constraint cname check (bar > 0),
@@ -15,7 +17,7 @@ create table foo (
 --- AST
 &ast.CreateTable{
   Create:      0,
-  Rparen:      447,
+  Rparen:      573,
   IfNotExists: false,
   Name:        &ast.Ident{
     NamePos: 13,
@@ -138,12 +140,12 @@ create table foo (
     &ast.ColumnDef{
       Null: -1,
       Name: &ast.Ident{
-        NamePos: 356,
-        NameEnd: 360,
+        NamePos: 482,
+        NameEnd: 486,
         Name:    "quux",
       },
       Type: &ast.ScalarSchemaType{
-        NamePos: 361,
+        NamePos: 487,
         Name:    "JSON",
       },
       NotNull:       false,
@@ -152,25 +154,25 @@ create table foo (
       Options:       (*ast.ColumnDefOptions)(nil),
     },
     &ast.ColumnDef{
-      Null: 389,
+      Null: 515,
       Name: &ast.Ident{
-        NamePos: 369,
-        NameEnd: 374,
+        NamePos: 495,
+        NameEnd: 500,
         Name:    "corge",
       },
       Type: &ast.ScalarSchemaType{
-        NamePos: 375,
+        NamePos: 501,
         Name:    "TIMESTAMP",
       },
       NotNull:     true,
       DefaultExpr: &ast.ColumnDefaultExpr{
-        Default: 394,
-        Rparen:  422,
+        Default: 520,
+        Rparen:  548,
         Expr:    &ast.CallExpr{
-          Rparen: 421,
+          Rparen: 547,
           Func:   &ast.Ident{
-            NamePos: 403,
-            NameEnd: 420,
+            NamePos: 529,
+            NameEnd: 546,
             Name:    "current_timestamp",
           },
           Distinct: false,
@@ -186,9 +188,10 @@ create table foo (
       ConstraintPos: 0,
       Name:          (*ast.Ident)(nil),
       Constraint:    &ast.ForeignKey{
-        Foreign: 182,
-        Rparen:  222,
-        Columns: []*ast.Ident{
+        Foreign:     182,
+        Rparen:      222,
+        OnDeleteEnd: -1,
+        Columns:     []*ast.Ident{
           &ast.Ident{
             NamePos: 195,
             NameEnd: 198,
@@ -207,65 +210,126 @@ create table foo (
             Name:    "t2key1",
           },
         },
+        OnDelete: "",
       },
     },
     &ast.TableConstraint{
-      ConstraintPos: 226,
+      ConstraintPos: 0,
+      Name:          (*ast.Ident)(nil),
+      Constraint:    &ast.ForeignKey{
+        Foreign:     226,
+        Rparen:      266,
+        OnDeleteEnd: 284,
+        Columns:     []*ast.Ident{
+          &ast.Ident{
+            NamePos: 239,
+            NameEnd: 242,
+            Name:    "bar",
+          },
+        },
+        ReferenceTable: &ast.Ident{
+          NamePos: 255,
+          NameEnd: 257,
+          Name:    "t2",
+        },
+        ReferenceColumns: []*ast.Ident{
+          &ast.Ident{
+            NamePos: 259,
+            NameEnd: 265,
+            Name:    "t2key2",
+          },
+        },
+        OnDelete: "ON DELETE CASCADE",
+      },
+    },
+    &ast.TableConstraint{
+      ConstraintPos: 0,
+      Name:          (*ast.Ident)(nil),
+      Constraint:    &ast.ForeignKey{
+        Foreign:     288,
+        Rparen:      328,
+        OnDeleteEnd: 348,
+        Columns:     []*ast.Ident{
+          &ast.Ident{
+            NamePos: 301,
+            NameEnd: 304,
+            Name:    "baz",
+          },
+        },
+        ReferenceTable: &ast.Ident{
+          NamePos: 317,
+          NameEnd: 319,
+          Name:    "t2",
+        },
+        ReferenceColumns: []*ast.Ident{
+          &ast.Ident{
+            NamePos: 321,
+            NameEnd: 327,
+            Name:    "t2key3",
+          },
+        },
+        OnDelete: "ON DELETE NO ACTION",
+      },
+    },
+    &ast.TableConstraint{
+      ConstraintPos: 352,
       Name:          &ast.Ident{
-        NamePos: 237,
-        NameEnd: 243,
+        NamePos: 363,
+        NameEnd: 369,
         Name:    "fkname",
       },
       Constraint: &ast.ForeignKey{
-        Foreign: 244,
-        Rparen:  297,
-        Columns: []*ast.Ident{
+        Foreign:     370,
+        Rparen:      423,
+        OnDeleteEnd: -1,
+        Columns:     []*ast.Ident{
           &ast.Ident{
-            NamePos: 257,
-            NameEnd: 260,
+            NamePos: 383,
+            NameEnd: 386,
             Name:    "foo",
           },
           &ast.Ident{
-            NamePos: 262,
-            NameEnd: 265,
+            NamePos: 388,
+            NameEnd: 391,
             Name:    "bar",
           },
         },
         ReferenceTable: &ast.Ident{
-          NamePos: 278,
-          NameEnd: 280,
+          NamePos: 404,
+          NameEnd: 406,
           Name:    "t2",
         },
         ReferenceColumns: []*ast.Ident{
           &ast.Ident{
-            NamePos: 282,
-            NameEnd: 288,
+            NamePos: 408,
+            NameEnd: 414,
             Name:    "t2key1",
           },
           &ast.Ident{
-            NamePos: 290,
-            NameEnd: 296,
+            NamePos: 416,
+            NameEnd: 422,
             Name:    "t2key2",
           },
         },
+        OnDelete: "",
       },
     },
     &ast.TableConstraint{
       ConstraintPos: 0,
       Name:          (*ast.Ident)(nil),
       Constraint:    &ast.Check{
-        Check:  301,
-        Rparen: 316,
+        Check:  427,
+        Rparen: 442,
         Expr:   &ast.BinaryExpr{
           Op:   ">",
           Left: &ast.Ident{
-            NamePos: 308,
-            NameEnd: 311,
+            NamePos: 434,
+            NameEnd: 437,
             Name:    "foo",
           },
           Right: &ast.IntLiteral{
-            ValuePos: 314,
-            ValueEnd: 315,
+            ValuePos: 440,
+            ValueEnd: 441,
             Base:     10,
             Value:    "0",
           },
@@ -273,25 +337,25 @@ create table foo (
       },
     },
     &ast.TableConstraint{
-      ConstraintPos: 320,
+      ConstraintPos: 446,
       Name:          &ast.Ident{
-        NamePos: 331,
-        NameEnd: 336,
+        NamePos: 457,
+        NameEnd: 462,
         Name:    "cname",
       },
       Constraint: &ast.Check{
-        Check:  337,
-        Rparen: 352,
+        Check:  463,
+        Rparen: 478,
         Expr:   &ast.BinaryExpr{
           Op:   ">",
           Left: &ast.Ident{
-            NamePos: 344,
-            NameEnd: 347,
+            NamePos: 470,
+            NameEnd: 473,
             Name:    "bar",
           },
           Right: &ast.IntLiteral{
-            ValuePos: 350,
-            ValueEnd: 351,
+            ValuePos: 476,
+            ValueEnd: 477,
             Base:     10,
             Value:    "0",
           },
@@ -303,8 +367,8 @@ create table foo (
     &ast.IndexKey{
       DirPos: -1,
       Name:   &ast.Ident{
-        NamePos: 439,
-        NameEnd: 442,
+        NamePos: 565,
+        NameEnd: 568,
         Name:    "foo",
       },
       Dir: "",
@@ -312,8 +376,8 @@ create table foo (
     &ast.IndexKey{
       DirPos: -1,
       Name:   &ast.Ident{
-        NamePos: 444,
-        NameEnd: 447,
+        NamePos: 570,
+        NameEnd: 573,
         Name:    "bar",
       },
       Dir: "",
@@ -324,4 +388,4 @@ create table foo (
 }
 
 --- SQL
-CREATE TABLE foo (foo INT64, bar FLOAT64 NOT NULL, baz STRING(255) NOT NULL OPTIONS(allow_commit_timestamp = null), qux STRING(255) NOT NULL AS (concat(baz, "a")) STORED, quux JSON, corge TIMESTAMP NOT NULL DEFAULT (current_timestamp()), FOREIGN KEY (foo) REFERENCES t2 (t2key1), CONSTRAINT fkname FOREIGN KEY (foo, bar) REFERENCES t2 (t2key1, t2key2), CHECK (foo > 0), CONSTRAINT cname CHECK (bar > 0)) PRIMARY KEY (foo, bar)
+CREATE TABLE foo (foo INT64, bar FLOAT64 NOT NULL, baz STRING(255) NOT NULL OPTIONS(allow_commit_timestamp = null), qux STRING(255) NOT NULL AS (concat(baz, "a")) STORED, quux JSON, corge TIMESTAMP NOT NULL DEFAULT (current_timestamp()), FOREIGN KEY (foo) REFERENCES t2 (t2key1), FOREIGN KEY (bar) REFERENCES t2 (t2key2) ON DELETE CASCADE, FOREIGN KEY (baz) REFERENCES t2 (t2key3) ON DELETE NO ACTION, CONSTRAINT fkname FOREIGN KEY (foo, bar) REFERENCES t2 (t2key1, t2key2), CHECK (foo > 0), CONSTRAINT cname CHECK (bar > 0)) PRIMARY KEY (foo, bar)
diff --git a/testdata/result/statement/alter_table_add_constraint_foreign_key.sql.txt b/testdata/result/statement/alter_table_add_constraint_foreign_key.sql.txt
index 6491304..2f63b9a 100644
--- a/testdata/result/statement/alter_table_add_constraint_foreign_key.sql.txt
+++ b/testdata/result/statement/alter_table_add_constraint_foreign_key.sql.txt
@@ -19,9 +19,10 @@ alter table foo add constraint fkname foreign key (foo, bar) references t2 (t2ke
         Name:    "fkname",
       },
       Constraint: &ast.ForeignKey{
-        Foreign: 38,
-        Rparen:  91,
-        Columns: []*ast.Ident{
+        Foreign:     38,
+        Rparen:      91,
+        OnDeleteEnd: -1,
+        Columns:     []*ast.Ident{
           &ast.Ident{
             NamePos: 51,
             NameEnd: 54,
@@ -50,6 +51,7 @@ alter table foo add constraint fkname foreign key (foo, bar) references t2 (t2ke
             Name:    "t2key2",
           },
         },
+        OnDelete: "",
       },
     },
   },
diff --git a/testdata/result/statement/alter_table_add_constraint_foreign_key_on_delete_cascade.sql.txt b/testdata/result/statement/alter_table_add_constraint_foreign_key_on_delete_cascade.sql.txt
new file mode 100644
index 0000000..1cfcc42
--- /dev/null
+++ b/testdata/result/statement/alter_table_add_constraint_foreign_key_on_delete_cascade.sql.txt
@@ -0,0 +1,61 @@
+--- alter_table_add_constraint_foreign_key_on_delete_cascade.sql
+alter table foo add constraint fkname foreign key (foo, bar) references t2 (t2key1, t2key2) on delete cascade
+
+--- AST
+&ast.AlterTable{
+  Alter: 0,
+  Name:  &ast.Ident{
+    NamePos: 12,
+    NameEnd: 15,
+    Name:    "foo",
+  },
+  TableAlternation: &ast.AddTableConstraint{
+    Add:             16,
+    TableConstraint: &ast.TableConstraint{
+      ConstraintPos: 20,
+      Name:          &ast.Ident{
+        NamePos: 31,
+        NameEnd: 37,
+        Name:    "fkname",
+      },
+      Constraint: &ast.ForeignKey{
+        Foreign:     38,
+        Rparen:      91,
+        OnDeleteEnd: 109,
+        Columns:     []*ast.Ident{
+          &ast.Ident{
+            NamePos: 51,
+            NameEnd: 54,
+            Name:    "foo",
+          },
+          &ast.Ident{
+            NamePos: 56,
+            NameEnd: 59,
+            Name:    "bar",
+          },
+        },
+        ReferenceTable: &ast.Ident{
+          NamePos: 72,
+          NameEnd: 74,
+          Name:    "t2",
+        },
+        ReferenceColumns: []*ast.Ident{
+          &ast.Ident{
+            NamePos: 76,
+            NameEnd: 82,
+            Name:    "t2key1",
+          },
+          &ast.Ident{
+            NamePos: 84,
+            NameEnd: 90,
+            Name:    "t2key2",
+          },
+        },
+        OnDelete: "ON DELETE CASCADE",
+      },
+    },
+  },
+}
+
+--- SQL
+ALTER TABLE foo ADD CONSTRAINT fkname FOREIGN KEY (foo, bar) REFERENCES t2 (t2key1, t2key2) ON DELETE CASCADE
diff --git a/testdata/result/statement/alter_table_add_constraint_foreign_key_on_delete_no_action.sql.txt b/testdata/result/statement/alter_table_add_constraint_foreign_key_on_delete_no_action.sql.txt
new file mode 100644
index 0000000..a9fde93
--- /dev/null
+++ b/testdata/result/statement/alter_table_add_constraint_foreign_key_on_delete_no_action.sql.txt
@@ -0,0 +1,61 @@
+--- alter_table_add_constraint_foreign_key_on_delete_no_action.sql
+alter table foo add constraint fkname foreign key (foo, bar) references t2 (t2key1, t2key2) on delete no action
+
+--- AST
+&ast.AlterTable{
+  Alter: 0,
+  Name:  &ast.Ident{
+    NamePos: 12,
+    NameEnd: 15,
+    Name:    "foo",
+  },
+  TableAlternation: &ast.AddTableConstraint{
+    Add:             16,
+    TableConstraint: &ast.TableConstraint{
+      ConstraintPos: 20,
+      Name:          &ast.Ident{
+        NamePos: 31,
+        NameEnd: 37,
+        Name:    "fkname",
+      },
+      Constraint: &ast.ForeignKey{
+        Foreign:     38,
+        Rparen:      91,
+        OnDeleteEnd: 111,
+        Columns:     []*ast.Ident{
+          &ast.Ident{
+            NamePos: 51,
+            NameEnd: 54,
+            Name:    "foo",
+          },
+          &ast.Ident{
+            NamePos: 56,
+            NameEnd: 59,
+            Name:    "bar",
+          },
+        },
+        ReferenceTable: &ast.Ident{
+          NamePos: 72,
+          NameEnd: 74,
+          Name:    "t2",
+        },
+        ReferenceColumns: []*ast.Ident{
+          &ast.Ident{
+            NamePos: 76,
+            NameEnd: 82,
+            Name:    "t2key1",
+          },
+          &ast.Ident{
+            NamePos: 84,
+            NameEnd: 90,
+            Name:    "t2key2",
+          },
+        },
+        OnDelete: "ON DELETE NO ACTION",
+      },
+    },
+  },
+}
+
+--- SQL
+ALTER TABLE foo ADD CONSTRAINT fkname FOREIGN KEY (foo, bar) REFERENCES t2 (t2key1, t2key2) ON DELETE NO ACTION
diff --git a/testdata/result/statement/alter_table_add_foreign_key.sql.txt b/testdata/result/statement/alter_table_add_foreign_key.sql.txt
index ef7e54c..7bf717f 100644
--- a/testdata/result/statement/alter_table_add_foreign_key.sql.txt
+++ b/testdata/result/statement/alter_table_add_foreign_key.sql.txt
@@ -15,9 +15,10 @@ alter table foo add foreign key (bar) references t2 (t2key1)
       ConstraintPos: 0,
       Name:          (*ast.Ident)(nil),
       Constraint:    &ast.ForeignKey{
-        Foreign: 20,
-        Rparen:  60,
-        Columns: []*ast.Ident{
+        Foreign:     20,
+        Rparen:      60,
+        OnDeleteEnd: -1,
+        Columns:     []*ast.Ident{
           &ast.Ident{
             NamePos: 33,
             NameEnd: 36,
@@ -36,6 +37,7 @@ alter table foo add foreign key (bar) references t2 (t2key1)
             Name:    "t2key1",
           },
         },
+        OnDelete: "",
       },
     },
   },
diff --git a/testdata/result/statement/create_table.sql.txt b/testdata/result/statement/create_table.sql.txt
index 08784aa..b0640aa 100644
--- a/testdata/result/statement/create_table.sql.txt
+++ b/testdata/result/statement/create_table.sql.txt
@@ -5,6 +5,8 @@ create table foo (
   baz string(255) not null options(allow_commit_timestamp = null),
   qux string(255) not null as (concat(baz, "a")) stored,
   foreign key (foo) references t2 (t2key1),
+  foreign key (bar) references t2 (t2key2) on delete cascade,
+  foreign key (baz) references t2 (t2key3) on delete no action,
   constraint fkname foreign key (foo, bar) references t2 (t2key1, t2key2),
   check (foo > 0),
   constraint cname check (bar > 0),
@@ -15,7 +17,7 @@ create table foo (
 --- AST
 &ast.CreateTable{
   Create:      0,
-  Rparen:      447,
+  Rparen:      573,
   IfNotExists: false,
   Name:        &ast.Ident{
     NamePos: 13,
@@ -138,12 +140,12 @@ create table foo (
     &ast.ColumnDef{
       Null: -1,
       Name: &ast.Ident{
-        NamePos: 356,
-        NameEnd: 360,
+        NamePos: 482,
+        NameEnd: 486,
         Name:    "quux",
       },
       Type: &ast.ScalarSchemaType{
-        NamePos: 361,
+        NamePos: 487,
         Name:    "JSON",
       },
       NotNull:       false,
@@ -152,25 +154,25 @@ create table foo (
       Options:       (*ast.ColumnDefOptions)(nil),
     },
     &ast.ColumnDef{
-      Null: 389,
+      Null: 515,
       Name: &ast.Ident{
-        NamePos: 369,
-        NameEnd: 374,
+        NamePos: 495,
+        NameEnd: 500,
         Name:    "corge",
       },
       Type: &ast.ScalarSchemaType{
-        NamePos: 375,
+        NamePos: 501,
         Name:    "TIMESTAMP",
       },
       NotNull:     true,
       DefaultExpr: &ast.ColumnDefaultExpr{
-        Default: 394,
-        Rparen:  422,
+        Default: 520,
+        Rparen:  548,
         Expr:    &ast.CallExpr{
-          Rparen: 421,
+          Rparen: 547,
           Func:   &ast.Ident{
-            NamePos: 403,
-            NameEnd: 420,
+            NamePos: 529,
+            NameEnd: 546,
             Name:    "current_timestamp",
           },
           Distinct: false,
@@ -186,9 +188,10 @@ create table foo (
       ConstraintPos: 0,
       Name:          (*ast.Ident)(nil),
       Constraint:    &ast.ForeignKey{
-        Foreign: 182,
-        Rparen:  222,
-        Columns: []*ast.Ident{
+        Foreign:     182,
+        Rparen:      222,
+        OnDeleteEnd: -1,
+        Columns:     []*ast.Ident{
           &ast.Ident{
             NamePos: 195,
             NameEnd: 198,
@@ -207,65 +210,126 @@ create table foo (
             Name:    "t2key1",
           },
         },
+        OnDelete: "",
       },
     },
     &ast.TableConstraint{
-      ConstraintPos: 226,
+      ConstraintPos: 0,
+      Name:          (*ast.Ident)(nil),
+      Constraint:    &ast.ForeignKey{
+        Foreign:     226,
+        Rparen:      266,
+        OnDeleteEnd: 284,
+        Columns:     []*ast.Ident{
+          &ast.Ident{
+            NamePos: 239,
+            NameEnd: 242,
+            Name:    "bar",
+          },
+        },
+        ReferenceTable: &ast.Ident{
+          NamePos: 255,
+          NameEnd: 257,
+          Name:    "t2",
+        },
+        ReferenceColumns: []*ast.Ident{
+          &ast.Ident{
+            NamePos: 259,
+            NameEnd: 265,
+            Name:    "t2key2",
+          },
+        },
+        OnDelete: "ON DELETE CASCADE",
+      },
+    },
+    &ast.TableConstraint{
+      ConstraintPos: 0,
+      Name:          (*ast.Ident)(nil),
+      Constraint:    &ast.ForeignKey{
+        Foreign:     288,
+        Rparen:      328,
+        OnDeleteEnd: 348,
+        Columns:     []*ast.Ident{
+          &ast.Ident{
+            NamePos: 301,
+            NameEnd: 304,
+            Name:    "baz",
+          },
+        },
+        ReferenceTable: &ast.Ident{
+          NamePos: 317,
+          NameEnd: 319,
+          Name:    "t2",
+        },
+        ReferenceColumns: []*ast.Ident{
+          &ast.Ident{
+            NamePos: 321,
+            NameEnd: 327,
+            Name:    "t2key3",
+          },
+        },
+        OnDelete: "ON DELETE NO ACTION",
+      },
+    },
+    &ast.TableConstraint{
+      ConstraintPos: 352,
       Name:          &ast.Ident{
-        NamePos: 237,
-        NameEnd: 243,
+        NamePos: 363,
+        NameEnd: 369,
         Name:    "fkname",
       },
       Constraint: &ast.ForeignKey{
-        Foreign: 244,
-        Rparen:  297,
-        Columns: []*ast.Ident{
+        Foreign:     370,
+        Rparen:      423,
+        OnDeleteEnd: -1,
+        Columns:     []*ast.Ident{
           &ast.Ident{
-            NamePos: 257,
-            NameEnd: 260,
+            NamePos: 383,
+            NameEnd: 386,
             Name:    "foo",
           },
           &ast.Ident{
-            NamePos: 262,
-            NameEnd: 265,
+            NamePos: 388,
+            NameEnd: 391,
             Name:    "bar",
           },
         },
         ReferenceTable: &ast.Ident{
-          NamePos: 278,
-          NameEnd: 280,
+          NamePos: 404,
+          NameEnd: 406,
           Name:    "t2",
         },
         ReferenceColumns: []*ast.Ident{
           &ast.Ident{
-            NamePos: 282,
-            NameEnd: 288,
+            NamePos: 408,
+            NameEnd: 414,
             Name:    "t2key1",
           },
           &ast.Ident{
-            NamePos: 290,
-            NameEnd: 296,
+            NamePos: 416,
+            NameEnd: 422,
             Name:    "t2key2",
           },
         },
+        OnDelete: "",
       },
     },
     &ast.TableConstraint{
       ConstraintPos: 0,
       Name:          (*ast.Ident)(nil),
       Constraint:    &ast.Check{
-        Check:  301,
-        Rparen: 316,
+        Check:  427,
+        Rparen: 442,
         Expr:   &ast.BinaryExpr{
           Op:   ">",
           Left: &ast.Ident{
-            NamePos: 308,
-            NameEnd: 311,
+            NamePos: 434,
+            NameEnd: 437,
             Name:    "foo",
           },
           Right: &ast.IntLiteral{
-            ValuePos: 314,
-            ValueEnd: 315,
+            ValuePos: 440,
+            ValueEnd: 441,
             Base:     10,
             Value:    "0",
           },
@@ -273,25 +337,25 @@ create table foo (
       },
     },
     &ast.TableConstraint{
-      ConstraintPos: 320,
+      ConstraintPos: 446,
       Name:          &ast.Ident{
-        NamePos: 331,
-        NameEnd: 336,
+        NamePos: 457,
+        NameEnd: 462,
         Name:    "cname",
       },
       Constraint: &ast.Check{
-        Check:  337,
-        Rparen: 352,
+        Check:  463,
+        Rparen: 478,
         Expr:   &ast.BinaryExpr{
           Op:   ">",
           Left: &ast.Ident{
-            NamePos: 344,
-            NameEnd: 347,
+            NamePos: 470,
+            NameEnd: 473,
             Name:    "bar",
           },
           Right: &ast.IntLiteral{
-            ValuePos: 350,
-            ValueEnd: 351,
+            ValuePos: 476,
+            ValueEnd: 477,
             Base:     10,
             Value:    "0",
           },
@@ -303,8 +367,8 @@ create table foo (
     &ast.IndexKey{
       DirPos: -1,
       Name:   &ast.Ident{
-        NamePos: 439,
-        NameEnd: 442,
+        NamePos: 565,
+        NameEnd: 568,
         Name:    "foo",
       },
       Dir: "",
@@ -312,8 +376,8 @@ create table foo (
     &ast.IndexKey{
       DirPos: -1,
       Name:   &ast.Ident{
-        NamePos: 444,
-        NameEnd: 447,
+        NamePos: 570,
+        NameEnd: 573,
         Name:    "bar",
       },
       Dir: "",
@@ -324,4 +388,4 @@ create table foo (
 }
 
 --- SQL
-CREATE TABLE foo (foo INT64, bar FLOAT64 NOT NULL, baz STRING(255) NOT NULL OPTIONS(allow_commit_timestamp = null), qux STRING(255) NOT NULL AS (concat(baz, "a")) STORED, quux JSON, corge TIMESTAMP NOT NULL DEFAULT (current_timestamp()), FOREIGN KEY (foo) REFERENCES t2 (t2key1), CONSTRAINT fkname FOREIGN KEY (foo, bar) REFERENCES t2 (t2key1, t2key2), CHECK (foo > 0), CONSTRAINT cname CHECK (bar > 0)) PRIMARY KEY (foo, bar)
+CREATE TABLE foo (foo INT64, bar FLOAT64 NOT NULL, baz STRING(255) NOT NULL OPTIONS(allow_commit_timestamp = null), qux STRING(255) NOT NULL AS (concat(baz, "a")) STORED, quux JSON, corge TIMESTAMP NOT NULL DEFAULT (current_timestamp()), FOREIGN KEY (foo) REFERENCES t2 (t2key1), FOREIGN KEY (bar) REFERENCES t2 (t2key2) ON DELETE CASCADE, FOREIGN KEY (baz) REFERENCES t2 (t2key3) ON DELETE NO ACTION, CONSTRAINT fkname FOREIGN KEY (foo, bar) REFERENCES t2 (t2key1, t2key2), CHECK (foo > 0), CONSTRAINT cname CHECK (bar > 0)) PRIMARY KEY (foo, bar)