🎓 A1Learner
🔍

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?