Sqlite Auto-Increment Non-Primary Key Field

SQLite auto-increment non-primary key field

You can do select max(id)+1 when you do the insertion.

For example:

INSERT INTO Log (id, rev_no, description)
VALUES ((SELECT MAX(id) + 1 FROM log), 'rev_Id', 'some description')

Note that this will fail on an empty table since there won't be a record with id is 0 but you can either add a first dummy entry or change the sql statement to this:

INSERT INTO Log (id, rev_no, description)
VALUES ((SELECT IFNULL(MAX(id), 0) + 1 FROM Log), 'rev_Id', 'some description')

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 make non primary key column is auto incremented

Unfortunately, it's not possible. The SQLite documentation on Autoincrement says at the bottom of the page:

Because AUTOINCREMENT keyword changes the behavior of the ROWID selection algorithm, AUTOINCREMENT is not allowed on WITHOUT ROWID tables or on any table column other than INTEGER PRIMARY KEY. Any attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column other than the INTEGER PRIMARY KEY column results in an error.

SQLite AUTO_INCREMENT id field not working

My guess is that you are using SQLite with phpliteadmin and not MySql, in which case this:

id INTEGER AUTO_INCREMENT PRIMARY KEY

is not the correct definition of the auto increment primary key.

In fact, the data type of this column is set to INTEGER AUTO_INCREMENT, as you can see in phpliteadmin, which according to 3.1. Determination Of Column Affinity, has INTEGER affinity.

Nevertheless it is the PRIMARY KEY of the table but this allows NULL values.

The correct syntax to have an integer primary key is this:

id INTEGER PRIMARY KEY AUTOINCREMENT 

Can i make non-primary key field as autogenerated value in room

If you go through THIS ANSWER, you will find that we can't have auto increment property in Composite Primary keys. So, the workaround would be to use concept of indexing and unique constraint. Make the role,deptt,grade columns as you indices and set the unique constraint as true. This will ensure that the pair of these three values are always unique(like primaryKey). Then add the techerId as Primarykey(autoGenerate = true) in the entity. Your entity class would look something like:

@Entity(tableName = "teacher",indices = arrayOf(Index(value = ["role","department","grade"],unique = true)))
public class Teacher{
    @PrimaryKey(autoGenerate = true)
    int teacher_id;

    @ColumnInfo(name = "name")
    String teacher_name;

    //Rest of the fields
    ......
    ......
}

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

why autoincrement primary key column not autoincrementing in sqlite3

It's not auto incrementing because you're already giving it an id, $nubits[0].

With no column list, INSERT INTO address VALUES (...) inserts all columns in the table. You could use NULL as @ReenactorRob suggests, but that just hides another problem with that query.

INSERT INTO address VALUES (...) requires knowledge of the order in which address was created to know that element 5 is street. If anything changes the ordering in the table, your INSERT breaks. If a column is added, your query breaks. If you put a value in the wrong slot (as you did) its difficult to tell that. You're much better off using an explicit column list.

INSERT INTO address
    (lastname, firstname, company, street, ...)
VALUES
    (...)

Now your ID will increment and you're protected from future table changes. If that seems like a lot of work, make it a function that takes a hash of values to build a query. It'll be much more readable than remembering what $nubits[12] is. But before you do that, look at DBIx::Class which has already done this for you.

I would be remiss if I didn't mention bind parameters. These are faster, allowing you to use prepared statements, and they protect you against SQL injection attacks.

---

Related Topics