CODEWITHSUNDEEP
javasqlite
user3484582
user3484582

Reputation: 557

Prevent autoincrement of id after IGNORE INTO statement in sqlite

I found a few duplicate threads talking about using option innodb_autoinc_lock_mode=0 but I don't know how to do that in SQLite.

Here is my code:

ps = conn.prepareStatement("INSERT OR IGNORE INTO users(name, lastname) VALUES(?, ?)");
ps.setString(1, nameField.getText());
ps.setString(2, lastNField.getText());
int res = ps.executeUpdate();

name and lastname are set as unique keys. This works as expected (doesn't insert if name and last name already exist) but autoincrements my id every time. Any idea how to approach this by either fixing the autoincrement or changing the sql code? I'm doing this in java.

Upvotes: 1

Views: 86

Answers (1)

CL.
CL.

Reputation: 180270

Just remove the AUTOINCREMENT from the table definition.

A plain INTEGER PRIMARAY KEY column still is autoincrementing, but does not keep a separate counter. Instead, new rows just get the next ID after the largest one currently in the table.

AUTOINCREMENT is useful only if you want to prevent the IDs from deleted rows from being reused.

Upvotes: 1

Related Questions