How can I create a sequence in SQLite?

In SQLite, since sequences (Sequence) are not directly supported, we typically use an auto-increment column in a table to emulate sequence functionality. The AUTOINCREMENT keyword in SQLite helps create an auto-increment column in a table, which is commonly used as the primary key. This approach ensures that the column value automatically increments with each new record insertion, mimicking the behavior of sequence generation.

How to Create a Table with an Auto-Increment Primary Key

Here is an example of creating a new table with an auto-increment primary key:

sql
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

In this example, the id column is defined as INTEGER PRIMARY KEY AUTOINCREMENT, meaning that when inserting new rows into the users table, the id value automatically increments, effectively simulating sequence functionality.

Insert Data Example

When inserting data, you do not need to explicitly specify the id value:

sql
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com');

After executing each insert operation, the id value automatically increments.

Verify Auto-Increment Behavior

You can confirm the auto-increment behavior by querying:

sql
SELECT * FROM users;

This will produce output similar to the following, where the id column starts at 1 and increments automatically with each inserted record:

shell
id   username   email
-------------------------
1    alice      alice@example.com
2    bob        bob@example.com

By leveraging AUTOINCREMENT, SQLite offers a straightforward and efficient method to emulate sequence functionality, making it ideal for applications that require auto-increment primary keys.