googleapis / googleapis/google-cloud-go

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

Open
#8,955 3 comments 0 reactions 1 assignee Claimed by @rahul2393 View on GitHub
priority: p3 type: bug
Dominant language
Go
Stars
4.5k
Forks
1.6k
Avg merge
1d 13h
Merged PRs (30d)
109

Description

**Client**

Spanner spansql

**Environment**

Go Playground

**Go Environment**

Go 1.21

**Code**

e.g.
```go
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 https://github.com/cloudspannerecosystem/wrench/issues/95

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.