Creating a table with ON DELETE CASCADE on a foreign key results in an exception

[jesse-shellabarger]

The Features and Limitations section of the README declares that ON CASCADE DELETE is unsupported and that a statement using this action will be replaced with NO ACTION.

This seems to be true only for interleaved tables. Creating a table with ON DELETE CASCADE on a foreign key results in an exception. ERROR: Foreign key referential action ON DELETE CASCADE is not supported. This operation does succeed on a real Spanner instance.

If this behavior is expected, I suggest updating the Features and Limitations section of the README to more clearly describe the difference between foreign keys and interleaved tables.

Repro Steps:

1. Start the pgadapter/emulator container

   
   docker pull gcr.io/cloud-spanner-pg-adapter/pgadapter-emulator

docker run -d -p 5555:5432 gcr.io/cloud-spanner-pg-adapter/pgadapter-emulator

2. Use psql to connect to the container

psql -h localhost -p 5555 -d test-db

3. Create a parent table

CREATE TABLE table_a (col_a integer, col_b integer, PRIMARY KEY (col_a));

4. Create a child table where cascading delete is on the interleaving. This one works.

CREATE TABLE child_a (col_a integer, col_c integer, PRIMARY KEY(col_a, col_c)) INTERLEAVE IN PARENT table_a ON DELETE CASCADE;

5. Create a child table where the cascading delete is on a plain old FK. This one fails.

CREATE TABLE child_b (col_a integer REFERENCES table_a(col_a) ON DELETE CASCADE, col_c integer, PRIMARY KEY(col_a));

[skuruppu]

@hengfengli would you be able to shed some light on this issue?

[sachinagargoogle]

Foreign Key (FK) ON DELETE CASCADE action feature was launched in Cloud Spanner in Q3-23. Earlier FK action wasn't available so this limitation was valid only for interleaved table referential actions, now we will have to explicitly call out that this behavior is specific to interleave tables.

OTOH, We will launch FK ON DELETE CASCADE action in Cloud Emulator soon, after that user will be able to create FK with ON DELETE CASCADE.