ON DELETE CASCADE action on foreign keys not working as expected

[evangelosdaniil]

Hi there,

I'm writing to report an issue with the Google Cloud Spanner emulator. The documentation states that Google Cloud Spanner supports the ON DELETE CASCADE action on foreign keys. However, the emulator does not seem to support this action, even in the latest version (1.5.9), which was supposed to have implemented this feature.

In previous versions of the emulator, we would get a parsing error when we tried to use the ON DELETE CASCADE action. However, in version 1.5.9, the DDL statement is executed successfully, but when querying the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS table, the DELETE_RULE column is set to NO_ACTION.

We have also tried running the same DDL statement on a real Spanner instance, and it works fine. The limitations under the README.md file do not state that foreign key actions are not supported, so we are wondering if the documentation needs to be updated or if we should wait for a new emulator version that will support the ON DELETE CASCADE action on foreign keys.

Thanks for your help!

Steps to reproduce the issue locally

1. Execute DDLs

CREATE TABLE Products (
      id STRING(36), 
      updated TIMESTAMP NOT NULL, 
      created TIMESTAMP NOT NULL
) PRIMARY KEY(id);

CREATE TABLE Orders (
      id STRING(36),
      productId STRING(36),
      created TIMESTAMP NOT NULL,
      updated TIMESTAMP NOT NULL,
      CONSTRAINT FK_ProductOrder FOREIGN KEY (productId) REFERENCES Products (id) ON DELETE CASCADE
) PRIMARY KEY (id);

2. Query the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS table. The DELETE_RULE column is set to NO_ACTION.

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;

--
CONSTRAINT_CATALOG  CONSTRAINT_SCHEMA  CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG  UNIQUE_CONSTRAINT_SCHEMA  UNIQUE_CONSTRAINT_NAME  MATCH_OPTION  UPDATE_RULE  DELETE_RULE  SPANNER_STATE
                                       FK_ProductOrder                                                      PK_Products             SIMPLE        NO ACTION    NO ACTION    COMMITTED

[realark]

Also seeing this on emulator version 1.5.10

[gunjan-juyal]

I am looking into this and will revert soon

[evangelosdaniil]

Thanks, @gunjan-juyal. Much appreciated.

[gunjan-juyal]

@evangelosdaniil I have verified that the DELETE CASCADE referential action has not yet been enabled in Cloud Spanner Emulator. We expect to finish adding this feature by Q4-2023 end. The Readme is outdated and I will update the limitations section for now.

Also, you have mentioned:

However, in version 1.5.9, the DDL statement is executed successfully, but when querying the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS table, the DELETE_RULE column is set to NO_ACTION.

Can you please confirm if you you ran the DDL statement in some unit tests or against an actual emulator instance? The NO_ACTION action is the default referential integrity action, and once this feature is enabled then DELETE CASCADE action will also get enabled.

[evangelosdaniil]

Thanks for clarifying that, @gunjan-juyal. That's really helpful, thank you! I can confirm that I ran the DDL statement against a real emulator instance.

[mattayes]

Hi folks,

It looks like the changes that went out in v1.5.11 break with the latest version of the Go client (v1.51.0). When trying to create a foreign key with ON DELETE NO ACTION I get this error:

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

I believe there's a bug here: on line 1689 ddl_foreign_key.on_delete() is only compared to ddl::ForeignKey::ACTION_UNSPECIFIED when it should also be compared to ddl::ForeignKey::NO_ACTION.

---

Additionally, whereas before v1.5.11 I could use the emulator with ON DELETE CASCADE and it would use ON DELETE NO ACTION instead, now I can't use the emulator at all without using a separate schema for testing vs. prod, which feels like a major step back.

[adam2k]

@mattayes did you figure out a work around for this? I'm experiencing a similar issue when running DB migrations and it's unclear if it's related to this or the migration tool that I'm using: https://github.com/golang-migrate/migrate

[adam2k]

I've also opened an issue here if this is not the appropriate place for this discussion. https://github.com/golang-migrate/migrate/issues/1023

[mattayes]

@adam2k As a workaround we were using v1.5.10 instead of upgrading to v1.5.11+.

Looking at your ticket I'm pretty sure this is an emulator issue, not migrate.

It looks like this was fixed in v1.5.13 (thanks Spanner team!). I haven't verified myself yet, let me know if it works for you.

[adam2k]

@mattayes thanks! Yes, updating to 1.5.13 for the emulator and using the latest golang-migrate version resolved my issue. I was having problems because there was a short period where the versions were out of sync and it was causing the bugs.

[krak3n]

I am running:

• Spanner Emulator 1.5.13
• Spanner Go Client: v1.55.0
• Go Migrate: v4.17.0

Running a CREATE TABLE migration with ON DELETE CASCADE results in an error:

CREATE TABLE Something (
  SomethingId STRING(36) NOT NULL DEFAULT (GENERATE_UUID()),
  OtherId STRING(36) NOT NULL
  CONSTRAINT FK_Other FOREIGN KEY (OtherId) REFERENCES Other (OtherId) ON DELETE CASCADE
) PRIMARY KEY (SomethingId);

The error I get is:

details: rpc error: code = Unimplemented desc = Foreign key referential action ON DELETE CASCADE is not supported.

Is this behaviour expected? I thought ON DELETE CASCADE would be rewritten too ON DELETE NO ACTION?

If so this basically makes the emulator unusable in integration style tests. I appreciate the underlying behaviour of ON DELETE CASCADE does not exist but at least we could create the table.

This as @mattayes says feels like a step back if this is intentional.

[maxalencar]

I worked for me using the latest version of the emulator. v1.5.19