Is There Auto Increment in SQLite

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).

How to make two columns auto increment in Sqlite

First thing with SQLite AUTOINCREMENT doesn't actually increment the ID, using INTEGER PRIMARY KEY will result in much the same, other than when you've had 9223372036854775807 rows added. All AUTOINCREMENT does is enforce an increasing number (so when the largest rowid is reached an SQLITE FULL exception occurs), otherwise (without AUTOINCREMENT) free lower numbers can be allocated, thus potentially circumventing the SQLITE FULL exception.

In fact what INTEGER PRIMARY KEY (with or without AUTOINCREMENT) does is make the columnn alias of the rowid (a normally hidden column that is given a unique 64 bit signed integer).

One of the rules is that only a single column can have INTEGER PRIMARY KEY (or PRIMARY KEY) coded per table. Which is the issue that you are facing.

---

A Solution

A way to do what you wish is to utilise a TRIGGER that is triggered when a new row is inserted and use it to update the inserted row with a value that is suffixed with the OID. e.g.

CREATE TRIGGER IF NOT EXISTS increment_tax_number 
    AFTER INSERT ON Person_Table 
    BEGIN
        UPDATE Person_Table SET TxnNo = 'Txn no '||new.OID WHERE OID =  new.OID;
    END;
INSERT INTO Person_Table VALUES(null,'not a valid tax number as yet','Fred',15000,'2018-01-01','blah','more blah');
INSERT INTO Person_Table VALUES(null,'not a valid tax number as yet','Bert',25000,'2018-03-04','blah','more blah');
SELECT * FROM Person_Table;

For a new table the above results in :-

This solution could be incorporated by using :-

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table " + TABLE_NAME + " (OID INTEGER PRIMARY KEY AUTOINCREMENT," +
            "TxnNo TEXT, Name TEXT, Amount INTEGER,Date TEXT, Description TEXT,Description2 TEXT)");
    db.execsql("CREATE TRIGGER If NOT EXISTS increment_tax_number     AFTER INSERT ON Person_Table 
BEGIN
    UPDATE Person_Table SET TxnNo = 'Txn no '||new.OID WHERE OID =  new.OID;
END");
}

• You would need to delete the App's data, uninstall the App or increase the version number (i.e. use super(context, DATABASE_NAME, null, 2)). Noting that you would lose any existing data. To not lose data would be more complicated.

A More efficient solution

Of course, there is also the option of just utilising the OID as it appears that you want the numeric part appended to Txn No, so there is no need to even have a column that is a waste. The Txn No 1, Txn No 2 etc can be generated when required.

e.g. The following will generate the Txn No purely from the OID column :-

SELECT Name, 'Txn No '||OID AS txn_no,Amount,Description,Description2 FROM Person_Table; 

Resulting in :-

To incorporate this solution you don't need to do anything other than use suitable queries (although you may wish to do away with the TxnNo column)

More about AUTOINCREMENT

Using AUTOINCREMENT incurs overheads that are rarely required. The SQLite documentation includes :-

• 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.

• SQLite Autoincrement

The overheads are because when AUTOINCREMENT is used the algorithm used to determine the new rowid adds a second stage of getting the respective row from the sqlite_sequence table and then using the higher of this and the highest rowid in the table (without AUTOINCREMENT just the highest rowid in the table is used). So the overheads are having to maintain and access this additional table for every insert.

As such it would be more efficient to define your table with either :-

CREATE TABLE IF NOT EXISTS Person_Table (OID INTEGER PRIMARY KEY,TxnNo TEXT, Name TEXT, Amount INTEGER,Date TEXT, Description TEXT,Description2 TEXT);

• If you decide to have the TxnNo column

or :-

CREATE TABLE IF NOT EXISTS Person_Table (OID INTEGER PRIMARY KEY, Name TEXT, Amount INTEGER,Date TEXT, Description TEXT,Description2 TEXT);

• If using the derived Txn No (more efficient solution)

If I use SQLite auto increment on a column, does it automatically maintain an index on that column?

Effectively yes, but not really.

That is, all AUTOINCREMENT does is add a constraint that requires the value assigned to the column to be higher than any existing value, or higher than any value that has been used, in that column.

But there's more to it than that it is your_column INTEGER PRIMARY KEY (AUTOINCREMENT can only be used on such a column and there can only be 1 such column per table) makes that column an alias of the hidden rowid column.

The rowid is what is indexed, and is basically the most primary index and the most efficient, which always exists unless the table is defined using the WITHOUT ROWID keyword.

So an AUTOINCREMENT column is an alias of the rowid column and uses a differnt, more expensive algorithm than an alias of the rowid without AUTOINCREMENT.

That is without AUTOINCREMENT the value generated for the rowid column will find the maximum value in the table and increment it. Unless that value exceeds 9223372036854775807 in which case SQlite will make some attempts to find an unused lower value (normally between 1 and 9223372036854775807).

With AUTOINCREMENT the algorithm takes the higher value of the maximum value and a value stored in the table sqlite_sequence for the respective table and uses that (thus any deleted higher valueswill not be re-used). However if 9223372036854775807 has been used then an SQLITE_FULL error will be raised.

The following should be noted :-

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.

SQLite Autoincrement you may well want to read this.

Additional

regrading the comment :-

If I don't use AUTOINCREMENT I have to explicitly create unique
integer IDs and then insert them in database each time a new row is
inserted.

The following demonstrates that there is no requirement for AUTOINCREMENT:-

CREATE TABLE IF NOT EXISTS xyz (ID INTEGER PRIMARY KEY);
INSERT INTO xyz VALUES(null);
SELECT * FROM xyz;

After running twice the result is :-

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)");

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 :-

How to correctly set AUTO INCREMENT fo a column in SQLite, using Python?

In SQLite, INTEGER PRIMARY KEY column is auto-incremented. There is also an AUTOINCREMENT keyword. When used in INTEGER PRIMARY KEY AUTOINCREMENT, a
slightly different algorithm for Id creation is used.

#!/usr/bin/python

import sqlite3
data_person_name = [('Michael', 'Fox'),
                    ('Adam', 'Miller'),
                    ('Andrew', 'Peck'),
                    ('James', 'Shroyer'),
                    ('Eric', 'Burger')]

con = sqlite3.connect(":memory:")

with con:

    c = con.cursor()

    c.execute('''CREATE TABLE q1_person_name
                 (name_id INTEGER PRIMARY KEY,
                  first_name varchar(20) NOT NULL,
                  last_name varchar(20) NOT NULL)''')
    c.executemany('INSERT INTO q1_person_name(first_name, last_name) VALUES (?,?)', data_person_name)

    for row in c.execute('SELECT * FROM q1_person_name'):
        print(row)

This code now works OK.

c.executemany('INSERT INTO q1_person_name(first_name, last_name) VALUES (?,?)', data_person_name)

When using auto-increment, we have to explicitly state the column names, omitting the
one that is auto-incremented.

$ ./test.py 
(1, u'Michael', u'Fox')
(2, u'Adam', u'Miller')
(3, u'Andrew', u'Peck')
(4, u'James', u'Shroyer')
(5, u'Eric', u'Burger')

This is the output of the code example.

Create table with auto increment

You have coded PRIMARYKEY instead of PRIMARY KEY and you should also code INTEGER PRIMARY KEY as the column type should appear first.

There is no need, from your explanation, to code AUTOINCREMENT.

Not using AUTOINCREMENT will be more efficient and will as far as you are concerned do the same thing. i.e. if the value for the department_id is not supplied, then SQLite will automatically generate a value which will be 1 for the first row that is inserted and then typically 1 greater for the next row and so on (SQLite does not guarantee montonically increasing numbers).

SQLite Autoincrement which includes

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'd sugggest just using :-

create table Departments(department_id INTEGER PRIMARY KEY,department_name char,department_jobs char);

---

Related Topics