spanner/spansql: ON DELETE NO ACTION gets added when no foreign key action is specified

[toga4]

Client

Spanner spansql

Environment

Go Playground

Go Environment

Go 1.21

Code

e.g.

package main

import (
	"fmt"

	"cloud.google.com/go/spanner/spansql"
)

func main() {
	ddl, _ := spansql.ParseDDL("ddl", `CREATE TABLE Table2 (
	Table2ID STRING(MAX) NOT NULL,
	Table1ID STRING(MAX) NOT NULL,
	CONSTRAINT FK_Table1Table2 FOREIGN KEY(Table1ID) REFERENCES Table1(Table1ID)
) PRIMARY KEY(Table2ID);`)
	fmt.Println(ddl.List[0].SQL())
}

Expected behavior

Outputs without foreign key action

CREATE TABLE Table2 (
  Table2ID STRING(MAX) NOT NULL,
  Table1ID STRING(MAX) NOT NULL,
  CONSTRAINT FK_Table1Table2 FOREIGN KEY (Table1ID) REFERENCES Table1 (Table1ID),
) PRIMARY KEY(Table2ID)

Actual behavior

Outputs with ON DELETE NO ACTION added.

CREATE TABLE Table2 (
  Table2ID STRING(MAX) NOT NULL,
  Table1ID STRING(MAX) NOT NULL,
  CONSTRAINT FK_Table1Table2 FOREIGN KEY (Table1ID) REFERENCES Table1 (Table1ID) ON DELETE NO ACTION,
) PRIMARY KEY(Table2ID)

Additional context

The current behavior causes a problem when trying to apply DDL to the spanner-emulator using migration tools like wrench. Since the emulator does not support foreign key actions, this behavior results in the output of DDLs that are not accepted by the emulator.

Related to cloudspannerecosystem/wrench#95

[noahdietz]

Tagged as a bug for rotation triaging, seems kind of like a process/testing thing though. Feel free to change.

[the-wendell]

Just a friendly bump 😄

It looks like there is already a PR (#8968 ) ready to go for this so fingers crossed that this can be resolved soon.

This issue blocks our CI pipeline because we are relying on spanner emulator which does not support ON DELETE actions. We would really like to upgrade to the latest Go spanner SDK to take advantage of the open telemetry support (https://github.com/googleapis/google-cloud-go/blob/main/spanner/CHANGES.md#1570-2024-02-13)

I am sure that we are not the only ones blocked by this.

[graeme-verticalscope]

@the-wendell FYI, this workaround might work for you:

I was able to upgrade my go spanner version by using spanner emulator version 1.5.10, note it has to be that exact emulator version. Newer or older emulator versions break. (See cloudspannerecosystem/wrench#95 (comment) and GoogleCloudPlatform/cloud-spanner-emulator#147 (comment))