October 21, 2024

SQLite Primary Keys

How Primary Keys Work in SQLite and How This Differs from MySQL

What is a Primary Key in a Database?

Primary keys are crucial in relational databases. They uniquely identify each record and are used to access and reference data. They must be unique and not null within a table. In SQLite, primary keys are handled differently than in some other databases. This article explores primary keys in SQLite, their different forms, and some interesting quirks that make SQLite's approach unique.

Primary Keys in SQLite: `rowid`, `_rowid_`, and `oid`

SQLite uses a unique approach to primary keys, especially with its default integer primary key, which is usually implicitly created even if not explicitly defined.

By default, every row in an SQLite table has an automatically created integer primary key called rowid. This column is hidden and can also be accessed using the names _rowid_ and oid (for legacy compatibility). The rowid column is an auto-incrementing integer uniquely identifying each row.

How to Make A Custom Primary Key in SQLite

To define your own primary key in SQLite, you can specify a column as the primary key when creating a table. For example:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT,
    email TEXT
);

In this example, the id column is explicitly defined as the primary key. SQLite will enforce uniqueness on this column, ensuring that each value is unique within the table. As long as you specify that this column is an INTEGER type, it acts as an internal alias for the rowid key column. So you'll have your id column, but interestingly, you can still access it using rowid, _rowid_ or oid column names, and it will return the id column values like so:

SELECT rowid FROM users;

id
--
1
2

Choosing Not to Use the Default Integer Primary Key

SQLite allows you to define your own primary key that is not an integer, such as a text-based UUID. In this case, the normal rowid primary key will not be created. This may avoid storing a small amount of disk space in some scenarios (such as when you're using a second UNIQUE key in addition to the PRIMARY KEY when you don't directly use the rowid/PRIMARY KEY), but it's not generally recommended because:

There is No Significant Performance Gain: Skipping the rowid field doesn't provide a notable performance boost.
It May make Indexes Larger: Primary keys are indexed in SQLite for efficient querying. However, the index will take more space with large or complex primary key types like UUIDs.

Note that you can also explicitly create a table without the implicit rowid column by using the WITHOUT ROWID clause:

CREATE TABLE users (
    id TEXT PRIMARY KEY,
    username TEXT,
    email TEXT
) WITHOUT ROWID;

Instead of using a non-numeric primary key, it is generally recommended to stick with the default integer primary key for simplicity and efficiency. If there's another non-numeric key you need to use, it can be created as a separate UNIQUE column in the table. When you use UNIQUE constraints, SQLite automatically creates an index for that column, ensuring uniqueness and optimizing query performance.

CREATE TABLE users (
    id INTEGER PRIMARY KEY, -- Efficient integer primary key using rowid
    uuid TEXT UNIQUE, -- Non-numeric primary key: SQLite will create an index for this column
    username TEXT,
    email TEXT
);

This approach retains the benefits of the default integer primary key while allowing you to use a custom non-numeric key for specific requirements.

Understanding `AUTOINCREMENT` in SQLite

SQLite's default behavior is to auto-increment the rowid or any INTEGER PRIMARY KEY without explicitly using an AUTOINCREMENT keyword. However, the AUTOINCREMENT keyword can be used to modify this behavior in specific ways:

Default Auto-Increment Behavior:

By default, SQLite automatically assigns the next available integer to rowid (or custom primary keys), starting from 1 and incrementing by 1 for each new row. If the largest value is deleted, the rowid will fill the gap if the integer is available. This is different from many other databases, which don't reuse deleted IDs by default.

The maximum rowid value is 9223372036854775807 (2^63-1), which is the largest 64-bit signed integer. Once this value is reached, SQLite will randomly look for unused integer IDs. If it cannot find one, it will return a SQLITE_FULL error.
Using AUTOINCREMENT:
- Adding AUTOINCREMENT to an INTEGER PRIMARY KEY column changes the behavior of the primary key to be strictly increasing and non-repeating, even after deletions, much like MySQL or other databases. Instead of simply using the next available number, it keeps a record of the last highest value in a hidden table called sqlite_sequence and ensures that each new key is always higher than the previous maximum. This prevents SQLite from reusing primary key values even after rows are deleted, which can be important in many cases to avoid referencing incorrect data.
```
 CREATE TABLE users (
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     username TEXT,
     email TEXT
 );
```

There are drawbacks to using this, so it's not always recommended:

Performance Impact: The AUTOINCREMENT feature can slightly impact performance since SQLite must check the sqlite_sequence table to determine the next value. However, the impact is generally minimal unless the primary key values grow very large.
Primary Key Limits: Using AUTOINCREMENT can accelerate the exhaustion of available integer IDs because gaps are never reused, making it more likely to encounter the SQLITE_FULL error after nine quintillion inserts.

Conclusion

You should now have a good understanding of how primary keys work in SQLite, how to customize them to suit your needs, and how to use non-numeric primary keys effectively. SQLite makes it easy to create an efficient table that just works for most needs. Still, you also have the flexibility to define primary keys without rowid or change the auto increment behavior to avoid reusing deleted IDs. By understanding these concepts, you can design your SQLite databases more effectively and efficiently.