"FOREIGN KEY constraint failed" error with the geopackage driver

[weiznich]

Expected behavior and actual behavior.

I have a custom setup that use a libsqlite3 version that is build with -DSQLITE_DEFAULT_FOREIGN_KEYS=1 (which enables foreign key constraints by default. If I now try to create a new geopackage geometry and insert data in that geometry this process fails with a "FOREIGN KEY constraint failed" error.

It's possible to workaround this issue for libsqlite3 versions build with the mentioned feature flag by just setting OGR_SQLITE_PRAGMA=FOREIGN_KEYS=0 (i.e disabling the foreign key check). The produced sqlite database contains only valid foreign key relations afterwards, so it just seems to be an issue in the order of which certain SQL statements are executed. I would expect that gdal uses an order that always results in valid foreign key relations instead of the other way around. If someone points to the relevant code I might consider working on a fix.

Steps to reproduce the problem.

This problem can be reproduced by manually requiring sqlite to check the foreign key constraints by setting the OGR_SQLITE_PRAGMA=FOREIGN_KEYS=1 config flag while running the driver independently from your sqlite compile flags.
For example it can be reproduced by using ogr2ogr:

OGR_SQLITE_PRAGMA=FOREIGN_KEYS=1 ogr2ogr /tmp/test.gpkg some_file.shp            
ERROR 1: sqlite3_exec(INSERT INTO gpkg_geometry_columns (table_name,column_name,geometry_type_name,srs_id,z,m) VALUES ('some_file','geom','POINT',4326,0,0)) failed: FOREIGN KEY constraint failed
ERROR 1: Unable to write feature 0 from layer Ereignisabfrage 2022-10-11.
ERROR 1: Terminating translation prematurely after failed
translation of layer some_file (use -skipfailures to skip errors)

Operating system

Fedora 39

GDAL version and provenance

Observed with:

• GDAL 3.7.3, released 2023/10/30
• GDAL 3.8.3 (build from 654f490)

[jratike80]

I believe that the foreign key constraint that stops the insert is this:

FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name)
So before it is possible to insert a row into gpkg_geometry_columns there should be a row about the same table_name in the gpkg_contents table.
I guess it is practical to defer the insert into gpkg_contents because there are fields like min_x, min_y which are known only after all the data has been inserted.
From https://www.sqlite.org/foreignkeys.html:

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection. (Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.) The application can also use a PRAGMA foreign_keys statement to determine if foreign keys are currently enabled.

So maybe the solution would be to run PRAGMA foreign_keys = OFF; in the correct place. I think that the opposite PRAGMA foreign_keys = ON; is already handled.

BTW where the -DSQLITE_DEFAULT_FOREIGN_KEYS=1 option is documented?

[weiznich]

BTW where the -DSQLITE_DEFAULT_FOREIGN_KEYS=1 option is documented?

It's documented here:

https://www.sqlite.org/compile.html#default_foreign_keys