Foreign key referential action ON DELETE NO ACTION is not supported

[graeme-verticalscope]

Summary

It seems the emulator doesn't support the ON DELETE NO ACTION syntax when creating a table with a foreign key constraint.

I can work-around this by removing ON DELETE NO ACTION from my create statement, and the table is successfully created.

Unfortunately spanner's migration tool wrench is modifying my migration scripts by automatically adding ON DELETE NO ACTION, meaning I cannot apply my migration scripts using wrench and the spanner emulator.

I have existing migration scripts that I've applied in production and they used to work with the spanner emulator. But now they are failing with the latest versions of wrench and cloud-spanner-emulator. My assumption is that wrench is behaving properly by automaticaly adding ON DELETE NO ACTION, since that is the default behaviour anyways, so I'm posting here instead of in the wrench github repo.

Steps to reproduce

Create Table1

CREATE TABLE Table1 (
  Table1ID STRING(MAX) NOT NULL,
) PRIMARY KEY(Table1ID);

Try to create Table2

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);

Get error:

UNIMPLEMENTED: Foreign key referential action ON DELETE NO ACTION is not supported.

Successfully create Table2 without ON DELETE:

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);

[manu2]

Hey Graeme, Thanks for reaching out. It is correct that emulator presently doesn't support "ON DELETE NO ACTION". Also your work around of not having "ON DELETE NO ACTION" will work fine as by default, not having a "ON DELETE" clause will be functionally same as having "ON DELETE NO ACTION". To help you better, how did you start facing this issue? Was this action recently added to your schema or did you upgrade some tool, like wrench, after which this started happening?( I see from commit history in wrench that they recently upgraded the dependencies)

[graeme-verticalscope]

Hi Manu! I upgraded from wrench v1.5.0 to v1.6.0 and that caused the issue. But it seems to be caused by the dependency cloud.google.com/go/spanner which also got upgraded from v1.47.0 to v1.49.0.

I can also reproduce the issue with wrench v1.5.0 and cloud.google.com/go/spanner v1.49.0.

[graeme-verticalscope]

I figured out that wrench has started adding ON DELETE NO ACTION because an older version of the spanner emulator gives this error:

Error parsing Spanner DDL statement: 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) : Syntax error on line 4, column 82: Expecting ')' but found 'ON'
    Failed to execute migration, Error parsing Spanner DDL statement: CREATE TABLE Table2 

But my migration script doesn't actually contain ON DELETE NO ACTION, it looks like this:

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);

[manu2]

Got it. So if I correctly understand the issue, it looks like for a foreign key constraint without any action, wrench is automatically adding a "ON DELETE NO ACTION", even though you don't have it in your original schema. This looks like a wrench issue as the schema is getting modified during migration.

[graeme-verticalscope]

Yup you've understood it correctly. Okay I'll create an issue for wrench and close this ticket.

I wasn't sure whether it was wrench or the emulator that needed to change. Let's see if wrench can fix it so they don't modify the migration.

[graeme-verticalscope]

FYI: Using emulator version v1.5.10 is a workaround to upgrade wrench: https://github.com/GoogleCloudPlatform/cloud-spanner-emulator/issues/147#issuecomment-1839184657