Sqlite Multiple Autoincrement Columns

SQLite multiple Autoincrement Columns?

You can't have two autoincrement fields. You should use a single autoincrement field. Given that both fields would always have the same value for every row, there's no reason to have to such fields anyway.

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)

SQLite multi-Primary Key on a Table, one of them is Auto Increment

No, I don't think this is possible.

You can create a UNIQUE INDEX which has essentially the same effect as a PRIMARY KEY:

CREATE UNIQUE INDEX pk_index ON "table1"("field1","field2");

Besides, I fail to see the logic of your schema, that is -> if a column is autoincrement and you don't intend to mess with the values manually, it's going to be unique anyway, so it makes a good simple short primary key. Why the composite? You may have good reasons to make another index on the combination of columns, though.

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

Use of composite keys with an AUTOINCREMENT column in SQLite database

No, the column does not need to be a primary key. However, SQLite allows AUTOINCREMENT only if one key is set as primary key. Here multiple columns are set as Primary Key. Hence auto increment on one of them does not work.

Refer to the answer to this stackoverflow post :

Auto Increment on Composite Primary Key

---

Related Topics