How to Create an Auto Increment Column in SQLite

SQLite is an open-source, quick, and light relational database management system used in many different applications. One of the essential features of a database is the ability to generate unique sequential values for a column. In SQLite, this functionality is provided through auto increment columns. I’ll go through how to make an auto increment column in SQLite in this article.

Auto Increment Column in SQLite

An auto increment column is a column that automatically generates a unique sequential integer value whenever a new record is inserted into the table. Some databases call it an identity column, and in SQLite, it’s called an INTEGER PRIMARY KEY. This column serves as the table’s main key, making each entry in the table uniquely identifiable.

Making a Column with Auto Increment

In SQLite, adding an auto increment column is a straightforward operation. To add an auto increment column to a table, you must do the following steps:

Create a Table with an Auto Increment Column

The SQL syntax to create an auto increment column in SQLite is quite simple. Here’s an example of how to create a table with an auto increment column:

CREATE TABLE employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INT,
salary REAL
);

In the above example, we have created a table named employees with four columns. The id column is an auto increment column that will generate a unique integer value every time a new record is inserted into the table.

The AUTOINCREMENT keyword in SQLite is only compatible with integer fields. When this keyword is used with other data types such as TEXT or REAL, SQLite will return an error. For example, the following statement will return an error:

CREATE TABLE users (
id TEXT PRIMARY KEY AUTOINCREMENT,
name TEXT,
email TEXT
);

The AUTOINCREMENT keyword in SQLite serves to create an auto incrementing column. By including this keyword in the column definition, SQLite takes care of generating a fresh value for that column with every INSERT statement. These generated values are always unique and never null.

In cases where the AUTOINCREMENT value is not explicitly provided, SQLite determines a unique value for the column based on the highest value currently existing in the column. To illustrate, if a table has an “id” column with the largest value being 10, the subsequent value assigned to the column will be 11.

Inserting Data into the Auto Increment Column

Once you have created the table with the auto increment column, you can insert data into the table. Here’s the SQL syntax to insert data into the table:

INSERT INTO employees (name, age, salary)
VALUES ('John', 22, 32000),
('Jane', 25, 35000),
('David', 28, 39000);

In the above example, we have inserted three records into the employees table. As we have defined the id column as an auto increment column, it will generate unique integer values automatically whenever a new record is inserted.

Retrieving Data from the Auto Increment Column

To retrieve specific rows from the table, you can use the WHERE clause with the auto increment column. Here’s an example of how to retrieve rows from the employees table based on the id column:

SELECT * FROM employees WHERE id = 2;

In the above example, we are retrieving the row from the employees table where id is 2. As there can only be one row where id is 2, the query will return only one row.

Updating Data in the Auto Increment Column

To update specific rows in the table, you can use the UPDATE statement with the auto increment column. Here’s an example of how to update a row in the employees table based on the id column:

UPDATE employees SET age=23 WHERE id=1;

In the above example, we are updating the age of the employee where id is 1. As the id column is an auto increment column, it uniquely identifies each record in the table.

Deleting Data in the Auto Increment Column

To delete specific rows from the table, you can use the DELETE statement with the auto increment column. Here’s an example of how to delete a row from the employees table based on the id column:

DELETE FROM employees WHERE id=2;

Conclusion

It is possible to create Auto Increment columns with the commonly used relational database management system SQLite. Auto Increment columns are useful in organizing and keeping databases clean. The implementation of Auto Increment columns in SQLite is achieved by defining an Integer column with the Primary Key property set and the AUTOINCREMENT keyword defined.