Give Every Item Its Own ID

Suppose the lending room owns two cordless drills. They can share a name, but the catalog still needs to tell the physical drills apart.

These two records make that possible:

Now a projector arrives with the ID 41. The current schema would accept it, leaving two different items with the same ID.

The id column needs a rule that prevents this. In SQL, a primary key identifies one row in a table.

Protect the ID

Open schema.sql and change its first column from id INTEGER to id INTEGER PRIMARY KEY. Leave the other four columns as they are.

Here is the complete schema after that change:

CREATE TABLE items (
    id INTEGER PRIMARY KEY,
    asset_tag TEXT,
    name TEXT,
    category TEXT,
    loan_days INTEGER
);

Press Run and find id INTEGER PRIMARY KEY in the table definition SQLite prints.

Read that line from left to right. id names the column, INTEGER remains its affinity, and PRIMARY KEY adds the new rule. The other column declarations have not changed.

When you press Submit, SQLite will be given two same-named drills with different explicit IDs. Both should be accepted. It will then be given another item with an ID that is already in use, which should be rejected. You do not need to write those records in schema.sql; your task is to write the rule they must follow.

The lending program will supply every ID in this chapter. We only rely on the behavior you can see here: different explicit IDs are accepted, and a repeated explicit ID is rejected.

Notice what the primary key does not prevent. Both drills may still be called Cordless drill, because a name describes an item but does not necessarily identify one physical object. Choosing id as the primary key protects that distinction.

Your table can now tell its rows apart. It still accepts an item whose tag, name, category, or loan period has no value at all. The next rule will protect those required facts.