Databases
Primary Keys & Uniqueness
O(n) to check uniqueness with a plain scan. In practice it is O(1) average, because primary keys are automatically indexed. The check is really a fast lookup, not a full scan.
The idea, in plain English
A primary key is like a national ID number. Every person has exactly one, and no two people can share the same one. It is how a table guarantees that each row can be told apart from every other row. The database itself refuses to let a duplicate in.
How it works
- 1When you create a table, you mark one column, often called 'id', as the PRIMARY KEY.
- 2Every time you INSERT a new row, the database checks whether any existing row already has this same key value.
- 3If the value is already taken, the insert is rejected, with a 'UNIQUE constraint failed' error. The table never ends up with two rows sharing a primary key.
When you'd use it
Every table should have a primary key. It lets other tables refer back to a row reliably (see the Joining Tables lesson). It also lets the database tell rows apart, even if every other column is identical.
Common beginner mistakes
- Do not use a column that is not truly unique, such as 'name', as a primary key. Two people can both be named Ana.
- Do not assume the database will quietly ignore a duplicate insert. It errors out instead, and your code needs to handle that.
Try it — edit and run
Click the code to edit · press ⌘/Ctrl+↵ to run
Editable code. Tab and Shift+Tab indent. Press Escape, then Tab, to move focus out of the editor.
SQL: CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
INSERT id=1 name=Ana: ok
INSERT id=2 name=Bilal: ok
INSERT id=3 name=Cara: ok
INSERT id=2 name=Duplicate: rejected (UNIQUE constraint failed: users.id)
Final table: 1:Ana 2:Bilal 3:CaraNot sure this is the right topic? See the learning paths → or where this leads →