0

I have a table, without any geometry, loaded in QGIS 1.8.0 (from OSGeo4W) via the Add Vector Layer option. The table resides in a SQLite/SpatiaLite database, and has a field named "id" that was created with INTEGER PRIMARY KEY option. From SpatiaLite GUI or SQLite Manager in Firefox, I can add a new row using any unique 64-bit integer value for "id". However, an attempt to add the same row from QGIS results in "id" being autoincremented. My question is: Is there a way to override or fix this behavior?

According to http://www.sqlite.org/autoinc.html it is valid to assign a value (within constraints) to an INTEGER PRIMARY KEY field.

1 Answer 1

1

This is not a geospatial problem but an SQL problem.

Short answer: A column declared INTEGER PRIMARY KEY will autoincrement because PRIMARY KEY should always imply NOT NULL. ( see SQLite: Frequently Asked Questions: 1) How do I create an AUTOINCREMENT field., SQL As Understood By SQLite or StackOverflow: No autoincrement for Integer Primary key in sqlite3)

You need to use SQL to understand why:

  • If I create a table in the SQLite shell with

enter image description here

SQLite uses id instead of the internal primary key named "rowid" because the value of a PRIMARY KEY can not be null

enter image description here

  • If I want to impose a specific value to the PRIMARY KEY

enter image description here

but if the key exist : enter image description here

An if I continue without imposing a PRIMARY KEY, the autoincrement continue:

enter image description here

However, an attempt to add the same row from QGIS results in "id" being autoincremented

It is the normal behaviour, otherwise the value of the primary key is null

3
  • I think you missed the point. This is not an SQL issue because I get the expected result when using SQL. I entered a valid value for "id" in QGIS, and did not receive the expected result.
    – Scro
    Jul 27, 2013 at 11:41
  • Ok, I understand the problem, if you wan to use the table as a complete SQLite table, you must use the DB Manager plugin (Database/DBManager menu) or other one
    – gene
    Jul 27, 2013 at 15:38
  • Good idea to use DB Manager -- I forgot about that since I use other tools for db operations. Your answer is very thorough and would probably be helpful to someone searching for info, so I will accept it as correct within a few days absent any more specific information about QGIS. Thanks.
    – Scro
    Jul 27, 2013 at 17:10

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.