How to make two columns auto increment in Sqlite

I'm Using OID as a primary key with auto increment, but I want to make Txn No also auto increment. Is there any way to make it auto increment? I tried to use loop, but it seems doesn't work.

When I click the "Save" one time, Next time should be Txn No "2", but I can't think of it, because I used OID to Auto increment, so Txn No can't use it.

Here is My Code:

public class DatabaseHelper extends SQLiteOpenHelper
public static final String DATABASE_NAME = "Person.db";
public static final String TABLE_NAME = "Person_Table";
public static final String COL_1 = "OID";
public static final String COL_2 = "TxnNo";
public static final String COL_3 = "Name";
public static final String COL_4 = "Amount";
public static final String COL_5 = "Date";
public static final String COL_6 = "Description";
public static final String COL_7 = "Description2";

public DatabaseHelper(Context context)
super(context, DATABASE_NAME, null, 1);


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


@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
onCreate(db);


public boolean insertData(String TxnNo, String Name, String Amount, String Date, String Description, String Description2)
SQLiteDatabase db = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put(COL_2, TxnNo);
contentValues.put(COL_3, Name);
contentValues.put(COL_4, Amount);
contentValues.put(COL_5, Date);
contentValues.put(COL_6, Description);
contentValues.put(COL_7, Description2);
long result = db.insert(TABLE_NAME, null, contentValues);
if (result == -1)
return false;
else
return true;





3 Answers
3

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 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) new.OID WHERE OID = new.OID;
END");



super(context, DATABASE_NAME, null, 2)

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)

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


or :-

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


You can't have 2 AUTOINCREMENT variables in the table. But now you declere: TxnNo TEXT it's very strange.
You can look for these variants sql-auto-increment-several

AUTOINCREMENT

TxnNo TEXT

db.insert(TABLE_NAME, null, contentValues)

AUTOINCREMENT

INTEGER PRIMARY KEY

May be, Sqlite not providing autoincrement for two column.
So i have several ways

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.