Skip to content

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.

→ Download my free SQL Handbook!

THE VALLEY OF CODE

THE WEB DEVELOPER's MANUAL

You might be interested in those things I do:

  • Learn to code in THE VALLEY OF CODE, your your web development manual
  • Find a ton of Web Development projects to learn modern tech stacks in practice in THE VALLEY OF CODE PRO
  • I wrote 16 books for beginner software developers, DOWNLOAD THEM NOW
  • Every year I organize a hands-on cohort course coding BOOTCAMP to teach you how to build a complex, modern Web Application in practice (next edition February-March-April-May 2024)
  • Learn how to start a solopreneur business on the Internet with SOLO LAB (next edition in 2024)
  • Find me on X

Related posts that talk about database: