Insert – How to Use ‘OR IGNORE’ in SQLite3 Without Leaving Empty Primary Key IDs

insertsqlite

I use 'INSERT OR IGNORE INTO' in my main table which works fine but leaves empty primary key IDs when records are ignored.
This leads to big jumps in my primary key numbering because I re-scan part of the same input data every time.
Can I avoid this and re-set the ID to the last used value before next call?

thanks

Best Answer

Apparently, leaving out the autoincrement from the key definition does the job. The key still autoincrements itself but not when IGNORE is triggered. I found this solution here: https://stackoverflow.com/questions/47120708/sqlite-autoincrement-and-insert-or-ignore-will-produce-unused-autoincrement-key

Related Solutions

How to use CREATE TABLE … AS with Primary Key in SQLite

I suspect you're missing the difference between CREATE TABLE and CREATE TABLE AS (otherwise known as CTAS).

CREATE TABLE AS allows you to create a table from the resultset of a query.

For example:

CREATE TABLE PHILSUCKS AS ( SELECT PHIL, SUCKS FROM EGGS );

You could, instead of using CTAS, use a "normal" CREATE TABLE statement, then INSERT the rows manually. This allows you to specify the PRIMARY KEY and any constraints. eg:

CREATE TABLE PHILSUCKS
(
  PHIL   INTEGER PRIMARY KEY,
  SUCKS  INTEGER NOT NULL
);

INSERT INTO PHILSUCKS ( SELECT PHIL, SUCKS FROM EGGS );

Obviously, you can also create indexes etc too:

CREATE INDEX EGGSUCKING ON PHILSUCKS (SUCKS);

Hope that helps!

SQLite – Fixing Foreign Key Mismatch

last_insert_rowid() returns the rowid of the most recently executed INSERT statement in the same connection, which is not what you want.

You have to use the ID of the setting that you want to refer to.

Also, as documented, the PE_ID_PRINTERSETTINGS column needs to be the primary key, or have a UNIQUE constraint.

Best Answer

Related Solutions

How to use CREATE TABLE … AS with Primary Key in SQLite

SQLite – Fixing Foreign Key Mismatch

Related Question