sqlite 3 upsert into autoincrementing column

#Sqlite 3 upsert into autoincrementing column code#

INSERT INTO people (person_id,first_name,last_name) On top of that, if you delete some rows and insert a new row, SQLite will try to reuse the rowid values from the deleted rows.įirst, insert a row with the maximum value into the people table. If SQLite cannot find any unused integer, it will issue an SQLITE_FULL error. If your data reaches this maximum value and you attempt to insert a new row, SQLite will find an unused integer and uses it. If you don’t specify the rowid value or you use a NULL value when you insert a new row, SQLite automatically assigns the next sequential integer, which is one larger than the largest rowid in the table.

How does SQLite assign an integer value to the rowid column? In this case, the person_id column is actually the rowid column. This time, however, we add another column named person_id whose data type is INTEGER and column constraint is PRIMARY KEY: DROP TABLE people The following statement drops table people and recreates it. When you create a table that has an INTEGER PRIMARY KEY column, this column is the alias of the rowid column. Note that you can also refer to the rowid column using its aliases: _rowid_ and oid.

#Sqlite 3 upsert into autoincrementing column code#

People Code language: SQL (Structured Query Language) ( sql )Īs you can see clearly from the output, SQLite implicitly creates a column named rowid and automatically assigns an integer value whenever you insert a new row into the table. Third, query data from the people table using the following SELECT statement: SELECT rowid,

VALUES( 'John', 'Doe') Code language: SQL (Structured Query Language) ( sql ) Second, insert a row into the people table using the following INSERT statement: INSERT INTO people (first_name, last_name)

) Code language: SQL (Structured Query Language) ( sql ) The rowid column store 64-bit signed integer that uniquely identifies a row in the table.įirst, create a new table named people that has two columns: first_name, and last_name: CREATE TABLE people ( Whenever you create a table without specifying the WITHOUT ROWID option, you get an implicit auto-increment column called rowid. Summary: in this tutorial, you will learn about SQLite AUTOINCREMENT column attribute and when to use it in your table.