I've been trying to work out a way to create relationships at the database level of geopackages.
Simple scenario for example - Table 'clearing' is related to table 'sourcefile'. The solution is to setup a foreign key.
This question here provides some reference for foreign key setup in geopackages. Foreign Keys in Geopackage
At first I tried using DB Browser for SQLite to create the relationships. The thing I discovered first is that there is a limitation with SQLite database (and as such, Geopackages) which prevents the update of existing tables (ie: Alter table statements). Therefore, the foreign keys need to be setup during the 'CREATE' process.
The only way to create a table, with a Foreign Key, (that I know of) - is by using a SQL statement. The following statement will create the foreign keys successfully when run in DB Browser for SQlite. (where the tables are called _dev_permit and _dev_sourcefile)
CREATE TABLE "_dev_permit" (
"fid" INTEGER NOT NULL,
"sourcefile" TEXT UNIQUE,
PRIMARY KEY("fid" AUTOINCREMENT)
FOREIGN KEY (sourcefile)
references _dev_sourcefile (filename)
Whilst this creates the table successfully, what it doesn't do is make the table available when viewing in GIS applications (QGIS as example). This is because the above sql run from within DB Browser does not trigger the other statements which populate the gpkg_contents tables and other required tables and triggers.
So I tried to run the above sql, inside of QGIS, using the DB Manager tool. Before running it, I tested the GUI driven 'Create Table' function from inside of QGIS DB Manager. This created a table correctly AND also made the table available inside of QGIS, which - upon inspection, meant that the GUI also triggered the creation of records in the gpkg_contents tables and other required tables for a gpkg. But of course, no foreign key.
The GUI has a place to enter SQL statements, so I then tried that. The table was created, but ONLY that table. No entry in the gpkg_contents. Foreign key was there, but still essentially not a valid geopackage table. Screen grab below shows the Create Table function on the right, with the sql statement on the left.
The conclusion ive come to is that the Create Table Gui will trigger the other required processes for geopackage table creation, but the SQL execution will only execute the sql in the statement.
So in summary:
- The only way to create foreign keys is with an SQL statement and ONLY during the CREATE process.
- I'm not aware of any facility inside of QGIS or anywhere else, which will trigger the population of the required gpgk_* tables in the geopackage database when running the above required SQL.
So the question is - can someone describe what the process would be for creating a new geopackage table which has a foreign key??