Sqlite: Autoincrement Primary Key Questions

Is there AUTO INCREMENT in SQLite?

You get one for free, called ROWID. This is in every SQLite table whether you ask for it or not.

If you include a column of type INTEGER PRIMARY KEY, that column points at (is an alias for) the automatic ROWID column.

ROWID (by whatever name you call it) is assigned a value whenever you INSERT a row, as you would expect. If you explicitly assign a non-NULL value on INSERT, it will get that specified value instead of the auto-increment. If you explicitly assign a value of NULL on INSERT, it will get the next auto-increment value.

Also, you should try to avoid:

 INSERT INTO people VALUES ("John", "Smith");

and use

 INSERT INTO people (first_name, last_name) VALUES ("John", "Smith");

instead. The first version is very fragile — if you ever add, move, or delete columns in your table definition the INSERT will either fail or produce incorrect data (with the values in the wrong columns).

SQLite: autoincrement primary key questions

I'm not sure whether you're actually using SQLite according to the syntax of your example.

If you are, you may be interested in SQLite FAQ #1: How do I create an AUTOINCREMENT field?:

Short answer: A column declared INTEGER PRIMARY KEY will
autoincrement.

sqlite text as primary key vs autoincrement integers

SQLite does not automatically compress text. So the answer to your question is "no".

Should you use text or an auto-incrementing id as the primary key? This can be a complex question. But happily, the answer is that it doesn't make much difference. That said, there are some considerations:

• Integers are of fixed length. In general, fix length keys are slightly more efficient in B-tree indexes than variable length keys.
• If the strings are short (like 1 or 2 or 3 characters), then they may be shorter -- or no longer -- than integers.
• If you change the string (say, if it is originally misspelled), then using an "artificial" primary key makes this easy: just change the value in one table. Using the string itself as a key can result in lots of updates to lots of tables.

SQLite AUTOINCREMENT non-primary key column

I thought I could implement a vector clock instead, but it seems that
AUTOINCREMENT values only exist for primary key columns. Does SQLite
offer any other AUTOINCREMENT or AUTOINCREMENT-like option?

They are not in fact AUTOINCREMENT values rather a column with AUTOINCREMENT will be an alias of the rowid column; not because AUTOINCREMENT has been coded but because INTEGER PRIMARY KEY has been coded.

All coding AUTOINCREMENT does is add a constraint that an auto-generated value MUST be greater than any other existing or used value. This only in fact becomes apparent if when a rowid with the value of 9223372036854775807 exists. In which case an attempt to insert a new row with an auto-generated rowid (i.e. no value is specified for the rowid column or an alias thereof) will result in an SQLITE_FULL error.

Without AUTOINCREMENT and when the highest rowid is 9223372036854775807 (the highest possible value for a rowid) an attempt is made to use a free value, which would obviously be lower than 9223372036854775807.
SQLite Autoincrement

• You may wish to note the very first line of the linked page which says :-

• The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and
  disk I/O overhead and should be avoided if not strictly needed. It is
  usually not needed.

• I can't see any need from your description.

So what you want is a means of assigning a value for the column that is to be sorted that is 1 greater than the highest current value for that column, so it becomes the latest for sorting purposes, a subquery that retrieves max(the_column) + 1 would do what you wish. This could be in an UPDATE, TRIGGER or in an INSERT.

• rowid = max(rowid) + 1 is basically how SQLite assigns a value to rowid unless AUTOINCREMENT is used when 1 is added to the greater of max(rowid) and the value, for the respective table, obtained from the table sqlite_sequence (will only exist if AUTOINCREMENT is used). It is referencing and maintaining sqlite_sequence that incurs the penalties.

For example you could use the following (which eliminates the need for an additional column and the additional index) :-

-- SETUP THE DATA FOR TESTING
DROP TABLE IF EXISTS podcast_searchv1;

CREATE TABLE IF NOT EXISTS podcast_searchv1 (
        _id INTEGER NOT NULL PRIMARY KEY, 
        search TEXT NOT NULL UNIQUE
);

INSERT INTO podcast_searchv1 (search) 
VALUES('foo'),('bar'),('guide')
;

-- Show original data
SELECT * FROM podcast_searchv1;

-- DO THE UPDATE
UPDATE podcast_searchv1 SET search = 'new value', _id = (SELECT max(_id) + 1 FROM podcast_searchv1) WHERE search = 'foo';

-- Show the changed data
SELECT * FROM podcast_searchv1;

The results being :-

and then :-

SQLite id auto-increment

SQLite AUTOINCREMENT is a keyword used for auto incrementing a value of a field in the table.You do not need ID1.

See reference here

Please use this:

db.execSQL("create table " + TABLE__WORK + " (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,Name TEXT, Title TEXT, Time TEXT, Date TEXT)");

In what cases should the AUTOINCREMENT be used instead of the default ROW ID?

The documentation says that

the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

So it is appropriate to use the AUTOINCREMENT keyword when you need to prevent the reuse of ROWIDs from previously deleted rows. This might be needed if you have external references to those deleted rows, and must not confuse them with new rows.

SQLite PRIMARY key AutoIncrement doesn't work

From documentation:

A table created using CREATE TABLE AS has no PRIMARY KEY and no
constraints of any kind. The default value of each column is NULL.

You don't have to add UNIQUE constraint on a COLUMN that has PRIMARY KEY constraint.

Explanation:

A UNIQUE constraint is similar to a PRIMARY KEY constraint, except
that a single table may have any number of UNIQUE constraints.

Instead add NOT NULL.
This is why:

According to the SQL standard, PRIMARY KEY should always imply NOT
NULL. Unfortunately, due to a bug in some early versions, this is not
the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the
table is a WITHOUT ROWID table or the column is declared NOT NULL,
SQLite allows NULL values in a PRIMARY KEY column. SQLite could be
fixed to conform to the standard, but doing so might break legacy
applications. Hence, it has been decided to merely document the fact
that SQLite allowing NULLs in most PRIMARY KEY columns.

I recommend using this Column definition:

CREATE TABLE conversations (
    conversation_id INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT,
...
}

---

Related Topics