Does SQLite Support Auto-Incrementing Primary Keys?

• SQLite is a database management system that lets you store and manage information.
• A table within a database organizes data like rows and columns.
• Each row represents a single record, and columns define the categories for that data.
• A primary key is a special column, or set of columns, that uniquely identifies each row in a table. It's like a social security number for each record; no two rows can have the same value for the primary key.

Auto-Increment:

• Auto-increment is a feature that automatically generates unique ID values for new records inserted into a table.
• This is useful for primary keys because it saves you from manually assigning IDs and ensures they're always unique.

So, together:

• The question "Is there AUTO INCREMENT in SQLite?" asks if SQLite supports automatically generating IDs for primary keys.
• The answer is yes! SQLite offers auto-increment functionality.

Here's a catch:

• By default, SQLite assigns a unique ID (called a ROWID) to each row internally, even if you don't explicitly define a primary key.
• You can also create a custom integer column and use the AUTOINCREMENT keyword when defining the table to make that specific column the auto-incrementing primary key.

In summary:

• SQLite offers auto-incrementing functionality for primary keys.
• It can be used with a built-in ROWID or a custom integer column.

This example creates a table named people with two columns: first_name and last_name. SQLite will implicitly create a hidden ROWID column that auto-increments for each new record.

CREATE TABLE people (
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL
);

INSERT INTO people (first_name, last_name) VALUES ('John', 'Doe');
INSERT INTO people (first_name, last_name) VALUES ('Jane', 'Smith');

SELECT * FROM people;

This code will create the table, insert two rows, and then select all data from the table. Even though the ROWID isn't explicitly shown, it will be assigned unique integer values (1 for the first row and 2 for the second row) to identify each record.

Example 2: Using a custom auto-incrementing column

This example creates a table named products with three columns: id (integer primary key with auto-increment), name (text), and price (real).

CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES ('T-Shirt', 19.99);
INSERT INTO products (name, price) VALUES ('Coffee Mug', 8.50);

SELECT * FROM products;

1. Manual ID assignment:

   • This approach involves explicitly defining the ID value when inserting a new record.
   • It gives you more control over the ID scheme, but requires manual tracking to ensure uniqueness.

   INSERT INTO products (id, name, price) VALUES (100, 'T-Shirt', 19.99);
   INSERT INTO products (id, name, price) VALUES (200, 'Coffee Mug', 8.50);
   

2. Using a separate table for IDs:

   • Create a separate table to store a sequence of unique IDs.
   • When inserting a new record into your main table, first retrieve an ID from the sequence table and then use that ID in your main table insertion.
   • This method allows you to manage IDs centrally and potentially reuse IDs from deleted records.

   CREATE TABLE id_generator (
       id INTEGER PRIMARY KEY AUTOINCREMENT
   );
   
   CREATE TABLE products (
       id INTEGER NOT NULL,
       name TEXT NOT NULL,
       price REAL NOT NULL,
       FOREIGN KEY (id) REFERENCES id_generator(id)
   );
   
   -- Get next ID from sequence table
   SELECT id FROM id_generator;
   
   -- Insert record using retrieved ID
   INSERT INTO products (id, name, price) VALUES (/* retrieved ID */, 'T-Shirt', 19.99);