How Can I Auto-Increment a Non-Primary Key Field in SQLite?

Auto-Incrementing Non-Primary Key Fields in SQLite

In SQLite, it may be desirable to have a non-primary key field be automatically incremented upon each insertion, achieving a similar behavior to auto-incrementing primary keys. This allows for the management of sequential values within a table.

To achieve this, a workaround can be implemented by leveraging the MAX() aggregate function. Consider the following scenario: a log table with a primary key (id) for internal use and a revision number (rev_no) that needs to be auto-incremented.

CREATE TABLE Log(
  id INTEGER PRIMARY KEY,
  rev_no INT
);

To insert a new log entry with an auto-incremented revision number, we can use the following SQL statement:

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

This statement first calculates the maximum id value in the Log table and adds 1 to it, effectively generating the next available revision number. The resulting value is then used as the rev_no for the new log entry.

An alternative approach can be employed to handle empty tables:

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

This modified statement uses the IFNULL() function to ensure that the maximum id value is computed, even if the table is empty. By default, it will return 0.

The above is the detailed content of How Can I Auto-Increment a Non-Primary Key Field in SQLite?. For more information, please follow other related articles on the PHP Chinese website!