SQL Primary Keys
A primary key is a column that names each row in a table. No two rows in the table can share the same primary key value.
Build a table with a key
PRIMARY KEY marks tag_id as the name of each row. Two inserts add two rows with two different keys.
CREATE TABLE tags (
tag_id INTEGER PRIMARY KEY,
label TEXT
);
INSERT INTO tags VALUES (1, 'Sale');
INSERT INTO tags VALUES (2, 'New');
SELECT tag_id, label
FROM tags
ORDER BY tag_id;A key picks one row
The WHERE clause asks for tag_id 2. Exactly one row comes back, and its label is New.
CREATE TABLE tags (
tag_id INTEGER PRIMARY KEY,
label TEXT
);
INSERT INTO tags VALUES (1, 'Sale');
INSERT INTO tags VALUES (2, 'New');
SELECT label
FROM tags
WHERE tag_id = 2;A reused key is refused
The second insert reuses tag_id 1. The block ends with that insert, and the stated output is the refusal.
CREATE TABLE tags (
tag_id INTEGER PRIMARY KEY,
label TEXT
);
INSERT INTO tags VALUES (1, 'Sale');
INSERT INTO tags VALUES (1, 'Clearance');Example
CREATE TABLE tags (
tag_id INTEGER PRIMARY KEY,
label TEXT
);
INSERT INTO tags VALUES (1, 'Sale');
INSERT INTO tags VALUES (2, 'New');
INSERT INTO tags VALUES (1, 'Clearance');Key points
- PRIMARY KEY: the marker that makes a column name each row
- Write PRIMARY KEY after the column name and its type.
- Each primary key value can appear only once in the table.
- An insert that reuses a key value is refused.
- A key value picks out exactly one row.
In this example, CREATE TABLE builds tags and marks tag_id as the primary key. The first two inserts add rows with the keys 1 and 2. The third insert tries to reuse the key 1. SQLite refuses that insert and reports that the unique rule for tags.tag_id failed.
Try it Yourself
Edit the code, then press Run.
Running is simulated · DB Fiddle
Exercise
What happens when an insert reuses a primary key value?