addForeignKeyConstraint is not supported on sqlite

General Discussion
1

Hello, I think liquibase decided for wrong change sets when running “liquibase generate-changelog” on a sqlite database.

I have a very simple sqlite db.
I run “liquibase generate-changelog”
then I run “liquibase changelog-sync-sql” on that db so that liquibase can create its tables and know about the state of the db.
However, I get several lines of error that state “addForeignKeyConstraint is not supported on sqlite”.
I figured out, that is because liquibase generated the addForeignKeyConstraint change.
“- changeSet:
id: 1728909987381-22
author: me (generated)
changes:
- addForeignKeyConstraint:”

which makes no sense, since sqlite does not support adding constraints when altering tables.
https://www.sqlite.org/omitted.html

Instead, liquibase should have added those foreign key constraints inside a createTable change like so:
…
- column:
constraints:
nullable: false
foreignKeyName: fk_creditbucket_organization
referencedTableName: organization
referencedColumnNames: id
validateForeignKey: true
name: organization_id
type: BIGINT(2000000000)

Am I understanding something wrong?
How can I make it so that liquibase generate-changelog adds foreign key constrains on creating the table , so I don’t have to fix half of the changelog manually?

Cheers

2

Hi @MDee,

I’ve been trying the steps you mentioned and I could reproduce the exact same issue you have done. There is nothing wrong on what you are doing, it’s Liquibase not behaving as expected in this case. If you don’t mind I’ll create a GH issue for this and then I’ll share that link with you.

Thanks for bring this up to our attention!

Daniel.

3

I’ve created this issue. If you are ok, please close this thread, and lets keep discussing about that other issue in GH.

Also, if you are keen on fixing the created issue myself or someone else from the team will be happy to assist you to do so.

Thanks,
Daniel.

4

I also found this addForeignKeyConstraint documentation is inconsistent regarding SQLite · Issue #105 · liquibase/liquibase-docs · GitHub
And here if you scroll down:

you will find: SQLite Not Supported No

Which brings me to the conclusion that liquibase in gerneral can not really be used for SQLite.

Not cool