Interleaved tables are generated as normal Foreign Keys in snapshot

[marcindyjas]

Descripton:

constraintName is not generated in changelog for addForeignKeyConstraint operation.

Steps to reproduce:

Use for example DDL as below to create new table on Spanner:

CREATE TABLE TEST (
TEST_LOC STRING(8) NOT NULL,
) PRIMARY KEY(TEST_LOC);

CREATE TABLE TEST_CONTNT (
TEST_LOC STRING(8) NOT NULL,
) PRIMARY KEY(TEST_LOC),
INTERLEAVE IN PARENT TEST ON DELETE CASCADE;

Then run:
liquibase --url="jdbc:cloudspanner:/projects/<project>/instances/<instance>/databases/<database>" --changeLogFile=changeLog.yml generateChangeLog

ChangeLog contains addForeignKeyConstraint without "constraintName" - as below:

- addForeignKeyConstraint:
    baseColumnNames: TEST_LOC
    baseTableName: TEST_CONTNT
    deferrable: false
    initiallyDeferred: false
    onDelete: CASCADE
    onUpdate: RESTRICT
    referencedColumnNames: TEST_LOC
    referencedTableName: TEST

[olavloite]

The INTERLEAVE IN PARENT construct is not a normal FOREIGN KEY constraint. Cloud Spanner supports two types of relationships between tables:

1. Interleaved tables: The data of the child table is stored physically together with the parent table. This relationship can only be defined when the child table is created, it cannot be added or removed at a later moment.
2. Foreign keys: This is the normal foreign key relationship that most relational databases support.

The example in this issue uses INTERLEAVED IN PARENT which is not really supported by Liquibase. There's a code example in the repository on how to create such tables using Liquibase. I'll have a look and see if it would be possible to get Liquibase to generate that when generating a change log, but I have my doubts it will be possible.

[olavloite]

I've been looking into whether it would be feasible to generate the INTERLEAVE IN PARENT in the snapshots, but that is not possible, because:

1. There is no suitable element that can be included in the createTable change type for the child table. The parent/child relationship must be specified when the child table is created, so it cannot be a separate change type. The trick with modifySql that we use when a change type is created manually, is not applicable to snapshots, as these cannot contain anything like modifySql.
2. In order to support parent/child relationships in snapshots, we would also need to ensure that parent tables are listed in the snapshot before the child table. Liquibase currently lists table in a snapshot in alphabetical order, and then adds foreign key constraints as separate change sets after the tables. Changing the ordering of tables in a snapshot in Liquibase is not possible in an extension, except by replacing the entire snapshot feature of Liquibase by a custom implementation.

[olavloite]

@marcindyjas I'll close this issue and open a new one for the remaining issue: Interleaved table relationships are not included in a generated snapshot.