Primary Keys

In lesson 1, you met the primary key: the column that identifies each row, such as id of games. A primary key follows two rules: no two rows have the same value in it, and it's never NULL. Thanks to that, WHERE id = 5 always finds at most one row, and other tables can point at a row by its key.

You make a column the primary key by writing PRIMARY KEY after its type. A table of genres, for example:

CREATE TABLE genres (
    id INTEGER PRIMARY KEY,
    name TEXT
) STRICT;

In SQLite, INTEGER PRIMARY KEY does one more thing: when an INSERT leaves the column out, SQLite picks the number itself. It takes one more than the largest number in the table, or 1 if the table is empty. That's where the automatic IDs of the last lesson came from:

INSERT INTO genres (name)
VALUES ('RPG'), ('Racing'), ('Puzzle');

SELECT * FROM genres;

You can still give the ID yourself, but a number that's taken is refused. The error says UNIQUE, because a primary key is unique; you'll learn more about UNIQUE in a moment:

INSERT INTO genres (id, name)
VALUES (2, 'Sports');

Error on line 1: UNIQUE constraint failed: genres.id

Which column should be the primary key? Usually a column id with a number and nothing else. A name or an email may look unique too, but people change their emails, and two customers can have the same name. A number that never changes is the safest thing for other tables to point at.

Other databases make automatic IDs their own way, for example id SERIAL in PostgreSQL or id INT AUTO_INCREMENT in MySQL. In SQLite, you may also meet INTEGER PRIMARY KEY AUTOINCREMENT: it makes sure that the number of a deleted row is never used again, which you'll rarely need. And a primary key can also be made of several columns, like the one of order_items. You'll create one later in this lesson.

Typical mistake: INT PRIMARY KEY instead of INTEGER PRIMARY KEY. Elsewhere, INT and INTEGER mean the same, but only INTEGER PRIMARY KEY makes SQLite number the rows. With INT, the ID stays empty when the INSERT leaves it out, and a primary key can't be empty:

CREATE TABLE genres (
    id INT PRIMARY KEY,
    name TEXT
) STRICT;

INSERT INTO genres (name)
VALUES ('RPG');

Error on line 6: NOT NULL constraint failed: genres.id