SQL, Unique and Primary keys
How to create unique and primary keys in a SQL database
With a table created with this command:
CREATE TABLE people (
age INT NOT NULL,
name CHAR(20) NOT NULL
);
We can insert an item more than once.
And in particular, we can have columns that repeat the same value.
We can force a column to have only unique values using the UNIQUE
key constraint:
CREATE TABLE people (
age INT NOT NULL,
name CHAR(20) NOT NULL UNIQUE
);
Now if you try to add the ‘Flavio’ twice:
INSERT INTO people VALUES (37, 'Flavio');
INSERT INTO people VALUES (20, 'Flavio');
You’d get an error:
ERROR: duplicate key value violates unique constraint "people_name_key"
DETAIL: Key (name)=(Flavio) already exists.
A primary key is a unique key that has another property: it’s the primary way we identify a row in the table.
CREATE TABLE people (
age INT NOT NULL,
name CHAR(20) NOT NULL PRIMARY KEY
);
The primary key can be an email in a list of users, for example.
The primary key can be a unique id
that we assign to each record automatically.
Whatever that value is, we know we can use it to reference a row in the table.
→ I wrote 17 books to help you become a better developer, download them all at $0 cost by joining my newsletter
→ JOIN MY CODING BOOTCAMP, an amazing cohort course that will be a huge step up in your coding career - covering React, Next.js - next edition February 2025