addColumn breaks existing foreign keys

[Airblader]

Environment

Liquibase Version: 3.10.3

Liquibase Integration & Version: Spring Boot

Liquibase Extension(s) & Version:

Database Vendor & Version: SQLite

Operating System Type & Version: Linux (Kernel 5.13)

Description

We have an existing table with a foreign key constraint which is set to deleteCascade: true. Running an addColumn changeset on this table removes the ON DELETE CASCADE on the existing column. We are facing this with SQLite, but have not verified other vendors.

This seems to happen because Liquibase translates the addColumn into renaming the table, recreating it, copying data over and dropping the renamed table. When recreating it, it adds the foreign key constraint, but without the ON DELETE CASCADE.

2021-06-09 09:06:12.903 DEBUG 2508344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE catalog_tables (id VARCHAR(36) NOT NULL, name VARCHAR(255) NOT NULL, database_id VARCHAR(36) NOT NULL, proto_type VARCHAR(64) NOT NULL, proto_data VARCHAR NOT NULL, CONSTRAINT PK_CATALOG_TABLES PRIMARY KEY (id), CONSTRAINT fk_catalog_tables_catalog_databases FOREIGN KEY (database_id) REFERENCES catalog_databases(id) ON DELETE CASCADE)
2021-06-09 09:06:12.903 DEBUG 2508344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : 1 row(s) affected
2021-06-09 09:06:12.903  INFO 2508344 --- [           main] liquibase.changelog.ChangeSet            : Table catalog_tables created

…

2021-06-09 09:06:12.954 DEBUG 2508344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : ALTER TABLE catalog_tables RENAME TO catalog_tables_temporary
2021-06-09 09:06:12.955 DEBUG 2508344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE catalog_tables (id VARCHAR(36) NOT NULL, name VARCHAR(255) NOT NULL, database_id VARCHAR(36) NOT NULL, proto_type VARCHAR(64) NOT NULL, proto_data VARCHAR(2000000000, 10) NOT NULL, kind VARCHAR(5), CONSTRAINT PK_CATALOG_TABLES PRIMARY KEY (id), CONSTRAINT fk_catalog_tables_catalog_databases FOREIGN KEY (database_id) REFERENCES catalog_databases(id))
2021-06-09 09:06:12.955 DEBUG 2508344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : 1 row(s) affected
2021-06-09 09:06:12.955 DEBUG 2508344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO `catalog_tables` (`id`,`name`,`database_id`,`proto_type`,`proto_data`) SELECT `id`,`name`,`database_id`,`proto_type`,`proto_data` FROM `catalog_tables_temporary`
2021-06-09 09:06:12.955 DEBUG 2508344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : 0 row(s) affected
2021-06-09 09:06:12.955 DEBUG 2508344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : DROP TABLE catalog_tables_temporary
2021-06-09 09:06:12.955 DEBUG 2508344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : 0 row(s) affected## Steps To Reproduce

Here's a slightly redacted version of our changesets. I removed columns and indices which are irrelevant.

- changeSet:
    id: 2
    changes:
    - createTable:
        tableName: catalog_tables
        columns:
        - column:
            name: database_id
            type: varchar(36)
            constraints:
              nullable: false
              foreignKeyName: fk_catalog_tables_catalog_databases
              references: catalog_databases(id)
              deleteCascade: true

  - changeSet:
      id: add kind column
      changes:
        - addColumn:
            tableName: catalog_tables
            columns:
              - name: kind
                type: varchar(5)
                value: TABLE
                constraints:
                  nullable: true

Actual Behavior

The ON DELETE CASCADE of an existing column is removed when adding another column.

Expected/Desired Behavior

addColumn should not change behavior of existing constraints on other columns.

[Airblader]

Note that this is related to #1671, but not the same. In that issue it's about deleteCascade not being taken into account for the new column being added, but our issue is that the ON DELETE CASCADE is removed from an existing column.

[Airblader]

And since dropForeignKeyConstraint and addForeignKeyConstraint are not supported in SQLite there seems to be no easy way of fixing this. :-(

[nvoxland]

#1671 is different, but the code was related enough that fixed that along with this in #1970