Creating a Foreign Key constraint with empty ON DELETE causes error

[IDispose]

Per documentation CASCADE or NO ACTION is optional

FOREIGN KEY (column_name[, ... ] ) REFERENCESref_table(ref_column[, ... ] [ ON DELETE { CASCADE | NO ACTION } ] )

However when running a CREATE TABLE statement on the emulator with no ON DELETE throws the following exception

(details: rpc error: code = Unimplemented desc = Foreign key referential action ON DELETE NO ACTION is not supported.)

This regression was introduced in v1.5.11

[manu2]

Hey IDispose,
Thanks for reaching out. Emulator doesn't support foreign key actions presently. However Foreign key definitions without any action, i.e. of the following form should work:
FOREIGN KEY (column_name[, ... ] ) REFERENCES ref_table(ref_column[, ... ])

Can you help with more context on :

1. How you are using emulator and this feature? What stack and setup is this being used with?
2. How did you start facing the issue? Are you new to emulator or is this a new change you have made or did this happen after some upgrade/updates?

[IDispose]

@manu2
Thanks for responding.

We are using the emulator to test our migration scripts before code merge. The scripts havent changed in a while and works as expected in emulator version v1.5.10. Our primary language is golang The issue was introduced in v1.5.11

We create a docker container pulling in the most recent version of the emulator and run the migration SQL scripts. The FK reference DOES NOT have ON ACTION and this was working earlier.

[manu2]

Thanks for the info.
There is already a bug open for this in go client already: googleapis/google-cloud-go#8955
I believe the "cloud.google.com/go/spanner/spansql" package when processing the DDL is adding a "ON DELETE NO ACTION" automatically for cases when foreign key action is not present. Though this is functionally equivalent to not having a foreign key action, but this syntax is not yet supported in emulator and is hence failing for you.
It would be useful for visibility of the go client team if you can add your issue details on this same bug, so it can be prioritised it accordingly.

Meanwhile as a workaround, the change seems to have released in v1.48 of cloud spanner go client (googleapis/google-cloud-go#8296), so using an older version should solve the issue temporarily until the above bug is resolved.

[IDispose]

@manu2 thanks for linking the bug. I wonder why then does adding ON DELETE NO ACTION work in emulator v1.5.10?

[manu2]

There were changes made in v1.5.11 to restrict using foreign key actions until they are implemented in emulator. So either having ON DELETE NO ACTION or not having an ON DELETE clause both should be throwing an error( since the latter is getting converter to ON DELETE NO ACTION as mentioned in the go client bug).
You can use an older version(v1.5.10) meanwhile to unblock yourself. I will further discuss and see if we can make changes to not throw error for ON DELETE NO ACTION as it is supposed to be a no op anyways.

[graeme-verticalscope]

@manu2 thanks for linking the bug. I wonder why then does adding ON DELETE NO ACTION work in emulator v1.5.10?

Oh this is the workaround I need! Neither v1.5.4 or v1.5.11 support ON DELETE NO ACTION, but I can finally upgrade wrench by using v1.5.10.