Bug report #4569
Primary keys of type TEXT always null after creating feature in spatialite layer
|Affected QGIS version:||master||Regression?:||No|
|Operating System:||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||14480|
- Create a spatialite table with a primary key of type TEXT.
- Load the spatialite table in QGIS.
- Add a feature and type a non null value into the primary key.
- Save changes.
- The primary key is null.
- Download the attached archive and extract all files.
- Open the sample.qgs.
- Add a feature to Points1. You always get null values in point_id (TEXT PRIMARY KEY).
- Add a feature to Points2. You always get a constraint error (because of the NOT NULL constraint added).
- As a workaround Points3 has a NOT NULL UNIQUE constraint.
QGIS master, Ubuntu 11.10.
#4 Updated by Sandro Furieri over 7 years ago
The SpatiaLite data provider always assumes a
Primary Key of the INTEGER AUTOINCREMENT type.
please note: the AUTOINCREMENT clause "magically"
transforms any NULL value into some unique numeric
value not yet used as a key-value, thus effectively
hiding any related complexity.
the same behaviour isn't obviously possible to
support when the Primary Key is of the TEXT type.
I hope this will help to understand why using any
Primary Key not defined as INTEGER AUTOINCREMENT
isn't a good idea.
(and AFAIK, the same is for PostGIS)
#5 Updated by Goyo D over 7 years ago
Thsnks, Sandro. The problem is that this enforces artificial restrictions on the database schema. I'm happy so far using autoincrement primary keys but I can think of some contexts where it can be an issue.
Anyway if this behavoir is a strong design decision feel free to close the ticket as wontfix.
#7 Updated by Jürgen Fischer over 7 years ago
Jürgen Fischer wrote:
Fixed in changeset 72d7a238b76c2d9a137da7a7eebe9d7833509561.
The value of the primary key still defaults to
NULL unless the user changes it. The provider used to overwrite the primary key with NULL, even if the user decided to enter a different value.
Accidental changes could still be avoided by manually setting the edit widget of the field to "Hidden" or "Immutable".
BTW the postgres provider presets the value with the default expression (eg.
nextval('table_id_seq')). So the user has a similar option to change that or leave it as is.