Is a PK required for GeoPackage vector layers?

Question

According to geopackage.org's "Getting Started With GeoPackage" guide, features are stored in user-defined data tables. It seems like it says that these tables require a primary key:

Other than the geometry column and a primary key, the schema of a features table is up to the implementer.

(Emphasis mine)

Is that a hard requirement? The underlying SQLite probably doesn't require a primary key, but does the GeoPackage specification mandate each layer to have one?

---

Motivation:

I'm asking because QGIS 3.4.4 seems to handle this inconsistently. If you intersect two vector layers the resulting layer will often not have any unique attributes (except for maybe the geometry column if the input layers were topologic).

• If I try to store the result directly to a GeoPackage layer, features that would lead to UNIQUE constraint violations on the fid column (which corresponds to the fid of the first input layer and is apparently automatically chosen as the new GeoPackage layer's PK) are simply omitted, turning the resulting layer incomplete.
• If I store the result in a "temporary layer", the result is complete. fid of the first input layer becomes fid column of the temporary layer and fid of the second input layer becomes fid_2 there.
  • If I naïvely try to persist it in a GeoPackage layer using "make permanent", that fails due to UNIQUE constraint violations.
  • If I use "make permanent" but in the dialog remove fid from the "FID" field, I can save to a GeoPackage layer fine. (And loading from it confirms that all the features are still there and that no new unique column for a PK has been added.)
• If I start from a layer loaded from a GeoPackage which apparently has a PK (field fid has "Not Null" and "Unique" checked as restrictions in Layer properties > Attribute form) and in remove the fid field in Layer properties > Source Fields, QGIS refuses to save that layer when trying to leave "editing mode" for that layer.

(Not tagging with qgis, qgis-3.4, etc., as the question itself is about the file format and should be independent of what Software is being used.)

Answer 1

Every feature table in GeoPackage must have a primary key of type INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL. See section 2.1.6 in the standard or this part in the getting started document http://www.geopackage.org/guidance/getting-started.html#features

User-defined Data Tables

Features are stored in user-defined data tables. Each features table has exactly one geometry column, a BLOB. (The structure of this BLOB is described here.) The OGC Simple Features geometry types are the supported geometry types. Other than the geometry column and a primary key, the schema of a features table is up to the implementer. Properties (text, integer, or real) provide additional information about each feature. The GeoPackage standard has an example schema.

The user interface of QGIS made you to believe that you created a table without primary key when you removed text fid from the "FID" field in the dialog. However, even if you do that the table will still be created with a primary key but the PK field will just have no name. You can see the whole CREATE TABLE sentense from the internal tables of SQLITE with a query

SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'test_table'

The result is like this:

CREATE TABLE "test_table" ( "" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
"geom" MULTISURFACE, "ogr_pkid" TEXT(255), "id" TEXT(255));

QGIS creates the table in this way so that the mandatory part "INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL" will be found from the schema of the table as GeoPackage standard requires. However, SQLite creates such PK field in any case if a user does not explicitly deny it. Internally SQLite knows that field as ROWID https://www.sqlite.org/rowidtable.html.

QGIS does not show the ROWID field that really exists for the SQLite engine. Therefore it might be better if you do not leave the name of the FID field empty but give it an alias like "foo" or "fid2". Then the table will be created as CREATE TABLE "test_table" ( "foo" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,... and the "foo" field will be visible in QGIS.

Answer 2

See GeoPackage Encoding Standard Section 2.1.6. Requirement 29 say that there should be an explicit primary key. Clients must support views that cannot have a primary key. But if you do, the SQL commandVACUUM can change the key values of your features.