Skip to content

SQL, Unique and Primary keys

New Course Coming Soon:

Get Really Good at Git

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.

Are you intimidated by Git? Can’t figure out merge vs rebase? Are you afraid of screwing up something any time you have to do something in Git? Do you rely on ChatGPT or random people’s answer on StackOverflow to fix your problems? Your coworkers are tired of explaining Git to you all the time? Git is something we all need to use, but few of us really master it. I created this course to improve your Git (and GitHub) knowledge at a radical level. A course that helps you feel less frustrated with Git. Launching May 21, 2024. Join the waiting list!
→ Read my SQL Tutorial on The Valley of Code

Here is how can I help you: