feat(spanner/spansql): add support for foreign key actions (#8296)
Co-authored-by: rahul2393 <irahul@google.com>
diff --git a/spanner/spansql/parser.go b/spanner/spansql/parser.go
index 5a763ff..f33cbbd 100644
--- a/spanner/spansql/parser.go
+++ b/spanner/spansql/parser.go
@@ -2227,7 +2227,7 @@
/*
foreign_key:
- FOREIGN KEY ( column_name [, ... ] ) REFERENCES ref_table ( ref_column [, ... ] )
+ FOREIGN KEY ( column_name [, ... ] ) REFERENCES ref_table ( ref_column [, ... ] ) [ ON DELETE { CASCADE | NO ACTION } ]
*/
if err := p.expect("FOREIGN"); err != nil {
@@ -2253,6 +2253,14 @@
if err != nil {
return ForeignKey{}, err
}
+ // The ON DELETE clause is optional; it defaults to NoActionOnDelete.
+ fk.OnDelete = NoActionOnDelete
+ if p.eat("ON", "DELETE") {
+ fk.OnDelete, err = p.parseOnDelete()
+ if err != nil {
+ return ForeignKey{}, err
+ }
+ }
return fk, nil
}
diff --git a/spanner/spansql/parser_test.go b/spanner/spansql/parser_test.go
index 848960f..0f3a2ee 100644
--- a/spanner/spansql/parser_test.go
+++ b/spanner/spansql/parser_test.go
@@ -1555,6 +1555,59 @@
},
},
},
+ {
+ `CREATE TABLE tname1 (col1 INT64, col2 INT64, CONSTRAINT con1 FOREIGN KEY (col2) REFERENCES tname2 (col3) ON DELETE CASCADE) PRIMARY KEY (col1);
+ CREATE TABLE tname1 (col1 INT64, col2 INT64, CONSTRAINT con1 FOREIGN KEY (col2) REFERENCES tname2 (col3) ON DELETE NO ACTION) PRIMARY KEY (col1);
+ ALTER TABLE tname1 ADD CONSTRAINT con1 FOREIGN KEY (col2) REFERENCES tname2 (col3) ON DELETE CASCADE;
+ ALTER TABLE tname1 ADD CONSTRAINT con1 FOREIGN KEY (col2) REFERENCES tname2 (col3) ON DELETE NO ACTION;`,
+ &DDL{
+ Filename: "filename",
+ List: []DDLStmt{
+ &CreateTable{
+ Name: "tname1",
+ Columns: []ColumnDef{
+ {Name: "col1", Type: Type{Base: Int64}, Position: line(1)},
+ {Name: "col2", Type: Type{Base: Int64}, Position: line(1)},
+ },
+ Constraints: []TableConstraint{
+ {Name: "con1", Constraint: ForeignKey{Columns: []ID{"col2"}, RefTable: "tname2", RefColumns: []ID{"col3"}, OnDelete: CascadeOnDelete, Position: line(1)}, Position: line(1)},
+ },
+ PrimaryKey: []KeyPart{
+ {Column: "col1"},
+ },
+ Position: line(1),
+ },
+ &CreateTable{
+ Name: "tname1",
+ Columns: []ColumnDef{
+ {Name: "col1", Type: Type{Base: Int64}, Position: line(2)},
+ {Name: "col2", Type: Type{Base: Int64}, Position: line(2)},
+ },
+ Constraints: []TableConstraint{
+ {Name: "con1", Constraint: ForeignKey{Columns: []ID{"col2"}, RefTable: "tname2", RefColumns: []ID{"col3"}, OnDelete: NoActionOnDelete, Position: line(2)}, Position: line(2)},
+ },
+ PrimaryKey: []KeyPart{
+ {Column: "col1"},
+ },
+ Position: line(2),
+ },
+ &AlterTable{
+ Name: "tname1",
+ Alteration: AddConstraint{
+ Constraint: TableConstraint{Name: "con1", Constraint: ForeignKey{Columns: []ID{"col2"}, RefTable: "tname2", RefColumns: []ID{"col3"}, OnDelete: CascadeOnDelete, Position: line(3)}, Position: line(3)},
+ },
+ Position: line(3),
+ },
+ &AlterTable{
+ Name: "tname1",
+ Alteration: AddConstraint{
+ Constraint: TableConstraint{Name: "con1", Constraint: ForeignKey{Columns: []ID{"col2"}, RefTable: "tname2", RefColumns: []ID{"col3"}, OnDelete: NoActionOnDelete, Position: line(4)}, Position: line(4)},
+ },
+ Position: line(4),
+ },
+ },
+ },
+ },
}
for _, test := range tests {
got, err := ParseDDL("filename", test.in)
diff --git a/spanner/spansql/sql.go b/spanner/spansql/sql.go
index d59c779..39b317e 100644
--- a/spanner/spansql/sql.go
+++ b/spanner/spansql/sql.go
@@ -546,6 +546,7 @@
str := "FOREIGN KEY (" + idList(fk.Columns, ", ")
str += ") REFERENCES " + fk.RefTable.SQL() + " ("
str += idList(fk.RefColumns, ", ") + ")"
+ str += " ON DELETE " + fk.OnDelete.SQL()
return str
}
diff --git a/spanner/spansql/sql_test.go b/spanner/spansql/sql_test.go
index 82b1f18..c43fc12 100644
--- a/spanner/spansql/sql_test.go
+++ b/spanner/spansql/sql_test.go
@@ -718,6 +718,47 @@
reparseDDL,
},
{
+ &CreateTable{
+ Name: "tname1",
+ Columns: []ColumnDef{
+ {Name: "cname1", Type: Type{Base: Int64}, NotNull: true, Position: line(2)},
+ {Name: "cname2", Type: Type{Base: Int64}, NotNull: true, Position: line(3)},
+ },
+ Constraints: []TableConstraint{
+ {
+ Name: "con1",
+ Constraint: ForeignKey{Columns: []ID{"cname2"}, RefTable: "tname2", RefColumns: []ID{"cname3"}, OnDelete: NoActionOnDelete, Position: line(4)},
+ Position: line(4),
+ },
+ },
+ PrimaryKey: []KeyPart{
+ {Column: "cname1"},
+ },
+ Position: line(1),
+ },
+ `CREATE TABLE tname1 (
+ cname1 INT64 NOT NULL,
+ cname2 INT64 NOT NULL,
+ CONSTRAINT con1 FOREIGN KEY (cname2) REFERENCES tname2 (cname3) ON DELETE NO ACTION,
+) PRIMARY KEY(cname1)`,
+ reparseDDL,
+ },
+ {
+ &AlterTable{
+ Name: "tname1",
+ Alteration: AddConstraint{
+ Constraint: TableConstraint{
+ Name: "con1",
+ Constraint: ForeignKey{Columns: []ID{"cname2"}, RefTable: "tname2", RefColumns: []ID{"cname3"}, OnDelete: CascadeOnDelete, Position: line(1)},
+ Position: line(1),
+ },
+ },
+ Position: line(1),
+ },
+ `ALTER TABLE tname1 ADD CONSTRAINT con1 FOREIGN KEY (cname2) REFERENCES tname2 (cname3) ON DELETE CASCADE`,
+ reparseDDL,
+ },
+ {
&Insert{
Table: "Singers",
Columns: []ID{ID("SingerId"), ID("FirstName"), ID("LastName")},
diff --git a/spanner/spansql/types.go b/spanner/spansql/types.go
index 571a76b..b7be8f5 100644
--- a/spanner/spansql/types.go
+++ b/spanner/spansql/types.go
@@ -461,6 +461,7 @@
Columns []ID
RefTable ID
RefColumns []ID
+ OnDelete OnDelete
Position Position // position of the "FOREIGN" token
}
