SQLite Auto Increment

  Total Views : 20
  Comments : 0
 
  Keywords : SQLite set auto increment on primary key column with example, SQLite autoincrement to set auto increment field with example, SQLite autoincrement property to insert values in column example

Here we will learn SQLite auto increment with examples and how to use SQLite auto increment property with column to generate unique values while inserting data with example.

SQLite Auto Increment Property

In SQLite if we set auto increment property on column it will generate sequential unique numeric values automatically whenever we insert new record or row in table.

 

Generally, the auto increment property in SQLite can only work with numeric data types and its very useful to use with primary key constraints because primary key will always allow only unique values.

 

Suppose, in SQLite when we create a table without specifying AUTOINCREMENT option on column then SQLite automatically create ROWID column to identify table rows uniquely within the table.

Example of SQLite Table without AutoIncrement

Now we will see with examples of creating table in SQLite with and without AutoIncrement columns. Following is the query which we used to create Persons table without AutoIncrement column.

 

CREATE TABLE Persons

(first_name TEXT,

last_name TEXT);

If you observe above query we created table without defining any AutoIncrement property on columns. Now use following queries to insert data in table.

 

INSERT INTO Persons

VALUES('Vinay','Jariwala');

INSERT INTO Persons

VALUES('Shweta','Rana');

Once we done with insertion of data use following SELECT statement to check Persons table.

 

SELECT rowid,first_name,last_name FROM Persons;

 

rowid       first_name  last_name

----------  ----------  ----------

1           Vinay       Jariwala

2           Shweta      Rana

If you observe above result SQLite automatically created an implicit column rowid and assigns an integer value whenever we insert a new row.

 

Suppose if we create a table with INTEGER PRIMARY KEY but without specifying AUTOINCREMENT then this column will points to row_id column.

Example of SQLite Table with Primary Key

Following is the example of creating table with Primary Key.

 

DROP TABLE Persons;

CREATE TABLE Persons

(id INTEGER PRIMARY KEY,

first_name TEXT,

last_name TEXT);

If you observe above statement we are dropping old table Persons and recreating same Persons table with INTEGER PRIMARY KEY. Here the id column will act as a row_id.

 

Now, if we insert NULL value in id column, it won’t give any error and it will assign an integer value which is one greater than the largest rowid in the table because it’s primary key of table. In case if table contains no data means then it will start from 1.

 

In SQLite maximum rowid value is 9223372036854775807. In case if records reach this maximum limit then SQLite search for unused integer values and insert the new reccords in that values. If SQLite fails to find integer value then it will throw error. 

 

Following is the example of inserting new row with maximum rowid value.

 

INSERT INTO Persons

(id,first_name,last_name)

VALUES(9223372036854775807,'Vinay','Jariwala');

Now we will try to insert new row without specifying id column value using following SQLite statement.

 

INSERT INTO Persons

(first_name,last_name)

VALUES('Shweta','Jariwala');

Now we will check the data of Persons table with SELECT statement.

 

sqlite> SELECT * FROM Persons;

 

id                   first_name  last_name

-------------------  ----------  ----------

1017135683236837057  Vinay       Jariwala

9223372036854775807  Shweta       Jariwala

Example of SQLite Table with AutoIncrement

Now we will see how to create table with INTEGER PRIMARY KEY AUTOINCREMENT for that use following query to drop existing Persons table and recreate it with AUTOINCREMENT property on PRIMARY KEY column.

 

DROP TABLE Persons;

CREATE TABLE Persons

(id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT);

In SQLite the starting value for AUTOINCREMENT is 1 and it will increment by 1 for each new record.

 

Now we will insert new records in Persons table for that use following queries.

 

INSERT INTO Persons(first_name,last_name)

VALUES('Shweta','Rana');

Now we will check the data of Persons table with SELECT statement.

 

sqlite> SELECT * FROM Persons;

 

id          first_name  last_name

----------  ----------  ----------

1           Shweta      Rana

As we know that SQLite keep track of rowid in sqlite_sequence catalog and we can also check the values of sqlite_catalog using following query.

 

sqlite> SELECT * FROM sqlite_sequence;

 

name         seq

-----------  ----------

dept_master  5

emp_master   9

Employee     4

Persons      1

As we know AUTOINCREMENT property will start sequence with value “1” in case if we want set custom starting value then we need to update the sequence value for that use following query.

 

UPDATE sqlite_sequence SET seq 100 WHERE NAME 'Persons';

Once we update the sequence value again we will check the data of sqlite_sequence catalog using following SELECT statement.

 

sqlite> SELECT * FROM sqlite_sequence;

 

name         seq

-----------  ----------

dept_master  5

emp_master   9

Employee     4

Persons      100

Now, we will try to insert new record in Persons table using following statements.

 

INSERT INTO Persons(first_name,last_name)

VALUES('Shweta','Rana');

Once we done with insertion we will check the records using SELECT statement.

 

sqlite> SELECT * FROM Persons;

 

id          first_name  last_name

----------  ----------  ----------

1           Shweta      Rana

101         Shweta      Rana

Here, you can see that sequence is reset and started from 100. So here we got the values of id as 101.

 

Generally, in SQLite the AUTOINCREMENT is not necessary because the same functionality can be achieved by ROWID and it will impose extra CPU, memory, disk space and disk I/O overhead.

 

This is how we can use SQLite AUTOINCREMENT property in our applications based on our requirements.

 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User