SQLite Foreign Key Enable: Manual Enabling required (since Part-DB 2.15)

[jbtronics]

SQLite foreign key check

Unlike MySQL and PostgreSQL, Sqlite does not check foreign key relationships by default, and this did not happened so far when Part-DB was used with Sqlite as platform.

It helps against data corruption and is highly recommend though, therefore there is now a new DATABASE_SQLITE_ENFORCE_FOREIGN_KEYS env variable to enable foreign key checks for Sqlite.
For backwards compatibility reasons its disabled by default however. For new database instances it is riskless, but for existing databases which already contains invalid data that breaks the foreign keys it could cause issues. There is a console command to help migration however.

How to do the migration

1. Backup your database: You can do this via CLI or webUI, to prevent data losses, when the database gets changed later
2. Run php bin/console partdb:database:check-sqlite-foreign-keys (or sudo docker exec --user=www-data partdb php bin/console partdb:database:check-sqlite-foreign-keys to see if your database contains any violations.
3. If it says your database is clean, you can continue setting your DATABASE_SQLITE_ENFORCE_FOREIGN_KEYS env to 1. Otherwise:
4. Run php bin/console partdb:database:check-sqlite-foreign-keys --fix, it will update/delete invalid entries, so that they should not cause problems
5. Set DATABASE_SQLITE_ENFORCE_FOREIGN_KEYS=1 in your .env.local (in direct installation) or your docker-compose.yaml (for docker environments)

In the future the default value will change to 1 for everyone, it makes sense to do the migration as soon as possible.