Create table dialog (SpatiaLite) misses data type 'Serial'

[qgib]

Author Name: Reinhard Reiterer (Reinhard Reiterer)
Original Redmine Issue: 15071

Redmine category:db_manager

---

In the 'DB Manager' the create table dialog misses data type 'Serial' for SpatiaLite connections.

---

• db_manager_create_table_spatialite.png (Reinhard Reiterer)

---

Related issue(s): #23021 (relates)
Redmine related issue(s): 15079

---

[qgib]

Author Name: Jukka Rahkonen (Jukka Rahkonen)

---

SQLite does not have datatype "serial". INTEGER PRIMARY KEY [AUTOINCREMENT] comes closest to that. No other column than the primary key one can be autoincremented.

http://www.sqlite.com/datatype3.html
http://www.sqlite.com/lang_createtable.html#rowid
https://sqlite.org/autoinc.html

For my mind this is not a bug in QGIS.

[qgib]

Author Name: Reinhard Reiterer (Reinhard Reiterer)

---

I've just noticed that for new SpatiaLite tables an auto incrementing primary key (data type 'integer') is created automatically. For new PostGIS tables users have to set the data type to 'serial'. So this is neither a bug nor a missing feature, just the interface is a bit confusing. Feel free to close this ticket.

[qgib]

Author Name: Jukka Rahkonen (Jukka Rahkonen)

---

PostgreSQL/PostGIS and SQLite/SpatiaLite are rather alike but they are not at all identical.

In SQLite there is no choice in this case because "column INTEGER PRIMARY KEY" takes the values automatically from the same place as the ROWID. In PostgreSQL it is possible and valid to have PK in integer column which does not take values from a sequence.

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)

---

If you have any idea how to improve the confusing interface, please open a new issue.

---

• status_id was changed from Open to Closed

[qgib]

Author Name: Reinhard Reiterer (Reinhard Reiterer)

---

Most likely this is not an issue of high priority. However, here is my proposal to fix it: #23021 (comment)

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

• relates was configured as New PostGIS/SpatiaLite table - Inconsistent behavior of primary keys #23021