6

I have a problem with a selected table in a geopackage database. QGIS is preventing updates only, from happening on a particular table, in this database. QGIS is preventing editing of values, via the QGIS GUI's, on existing fields on this table.

I have a database called 'Design'. The table is called 'version'. This table is a non-geometry table. Attributes only.

I can toggle editing on the table. If i bring up the attribute table, or Form windows, the attribute fields are not editable. QGIS prevents the cursor from entering the field editing boxes and typing does nothing. Further to this, if I try to use the Field Calculator, the 'Update existing field' box is greyed out.

I can however, create a new record. (ie: Insert). I can insert records via various mechanisms.

Additionally, I can open the database in DB Browser, or SQLStudio applications, and edit the values of this table using those applications.

There are no non-standard triggers on this table (in fact, there are no update triggers, only insert and delete triggers).

The table does however, have a compound primary key. Two fields, are set as a primary key and are therefore, listed as a constraint in the dm software (SQLStudio as an example). The primary keys are fid and option_name.

I tested this by removing option_name as a primary key. Sure enough, editing capability was restored from within QGIS.

What I need to understand is, why does this primary key constraint prevent updates of other fields in QGIS? It does not prevent updates from within SQLStudio, so how does QGIS interpret this as a 'prevent all updates'.

1 Answer 1

5

Your attribute table with a compound primary key is not the kind of User Data Table that the GeoPackage standard defines

2.4.3. Attributes User Data Tables

Data

Table Definition

Non-spatial attribute data is stored in user-defined Attribute tables. Attribute sets are rows in an Attribute table. The attributes are columns in a Attribute table. (A GeoPackage is not required to contain any Attribute data tables. Attribute data tables in a GeoPackage may be empty.)

Requirement 119

A GeoPackage MAY contain tables or updatable views containing attribute sets. Every such Attribute table or view in a GeoPackage SHALL have a column with column type INTEGER and PRIMARY KEY AUTOINCREMENT column constraints per GeoPackage Attributes Example Table or View Definition and EXAMPLE: Attributes table Create Table SQL (Informative).

The integer primary key of an Attribute table allows attribute sets to be linked to row level metadata records in the gpkg_metadata table by rowid [B5] values in the gpkg_metadata_reference table as described in clause Metadata below.

It is undefined in the standard how a GeoPackage client should deal with tables and database structures which are not defined in the GeoPackage standard.

1
  • 1
    ok cool. So basically, whilst SQLite databases support them, Geopackages don't support compound primary keys on attribute tables, which would explain why QGIS spits the dummy, but SQLStudio doesn't. Thanks!
    – nr_aus
    Jun 16, 2022 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.