Step 8
8 min read

Primary Key

Learn what Primary Keys are and why every table needs one - explained in the simplest way possible.

What You Will Learn

Students Table with Primary Key
student_id (PRIMARY KEY)nameage
1John20
2Mary19
3Peter21
3 rows
Contacts Table - Bad Example (No Primary Key)
namephone
John555-0001
John555-0001
John555-0001
3 rows
Contacts Table - Good Example (With Primary Key)
contact_id (PRIMARY KEY)namephone
1John555-0001
2Mary555-0002
3Peter555-0003
3 rows
  • What a Primary Key is (in very simple words)
  • Why we need it
  • How to create one
  • Real-life examples you can relate to

What is a Primary Key?

Students Table with Primary Key
student_id (PRIMARY KEY)nameage
1John20
2Mary19
3Peter21
3 rows
Contacts Table - Bad Example (No Primary Key)
namephone
John555-0001
John555-0001
John555-0001
3 rows
Contacts Table - Good Example (With Primary Key)
contact_id (PRIMARY KEY)namephone
1John555-0001
2Mary555-0002
3Peter555-0003
3 rows

A Primary Key is like a student ID card number or your passport number. It is a unique number that identifies each row in your table.

Simple rule: No two rows can have the same Primary Key.

Real-Life Examples You Know

Students Table with Primary Key
student_id (PRIMARY KEY)nameage
1John20
2Mary19
3Peter21
3 rows
Contacts Table - Bad Example (No Primary Key)
namephone
John555-0001
John555-0001
John555-0001
3 rows
Contacts Table - Good Example (With Primary Key)
contact_id (PRIMARY KEY)namephone
1John555-0001
2Mary555-0002
3Peter555-0003
3 rows

Student ID Cards:

  • John has student ID: 12345
  • Mary has student ID: 67890
  • No two students can have the same ID

Phone Contacts:

  • Each contact has a unique entry
  • You cannot have two identical contacts

Library Books:

  • Each book has a unique barcode
  • Even if two books have the same title, each has different barcode

Why Do We Need Primary Keys?

Students Table with Primary Key
student_id (PRIMARY KEY)nameage
1John20
2Mary19
3Peter21
3 rows
Contacts Table - Bad Example (No Primary Key)
namephone
John555-0001
John555-0001
John555-0001
3 rows
Contacts Table - Good Example (With Primary Key)
contact_id (PRIMARY KEY)namephone
1John555-0001
2Mary555-0002
3Peter555-0003
3 rows

Imagine a school with 5 students named "John". How do you know which John you are talking about?

Without Primary Key: Confusion

  • "Update John's grade to A" - Which John?
  • "Delete John" - Which one?

With Primary Key: Clear

  • "Update student 12345's grade" - Exactly one student
  • "Delete student 67890" - No confusion

How to Create a Primary Key

Students Table with Primary Key
student_id (PRIMARY KEY)nameage
1John20
2Mary19
3Peter21
3 rows
Contacts Table - Bad Example (No Primary Key)
namephone
John555-0001
John555-0001
John555-0001
3 rows
Contacts Table - Good Example (With Primary Key)
contact_id (PRIMARY KEY)namephone
1John555-0001
2Mary555-0002
3Peter555-0003
3 rows

Example 1: Simple Students Table

CREATE TABLE students ( student_id INTEGER PRIMARY KEY, name VARCHAR(100), age INTEGER );

What this means:

  • student_id is the Primary Key
  • Every student gets a unique number
  • Cannot have two students with same student_id

Example 2: Auto-Generated IDs

Let the database create IDs automatically:

CREATE TABLE students ( student_id SERIAL PRIMARY KEY, name VARCHAR(100), age INTEGER );

What SERIAL does:

  • First student: student_id = 1 (automatic)
  • Second student: student_id = 2 (automatic)
  • Third student: student_id = 3 (automatic)
  • You do not need to provide the ID!

Primary Key Rules

Students Table with Primary Key
student_id (PRIMARY KEY)nameage
1John20
2Mary19
3Peter21
3 rows
Contacts Table - Bad Example (No Primary Key)
namephone
John555-0001
John555-0001
John555-0001
3 rows
Contacts Table - Good Example (With Primary Key)
contact_id (PRIMARY KEY)namephone
1John555-0001
2Mary555-0002
3Peter555-0003
3 rows
  1. Must be unique - No duplicates allowed
  2. Cannot be NULL - Must have a value
  3. Only one per table - Each table has one Primary Key
  4. Never changes - Once set, do not change it

Inserting Data with Primary Keys

Students Table with Primary Key
student_id (PRIMARY KEY)nameage
1John20
2Mary19
3Peter21
3 rows
Contacts Table - Bad Example (No Primary Key)
namephone
John555-0001
John555-0001
John555-0001
3 rows
Contacts Table - Good Example (With Primary Key)
contact_id (PRIMARY KEY)namephone
1John555-0001
2Mary555-0002
3Peter555-0003
3 rows

With Manual IDs

INSERT INTO students (student_id, name, age) VALUES (1, 'John', 20); INSERT INTO students (student_id, name, age) VALUES (2, 'Mary', 19); -- This fails (ID 1 already exists): INSERT INTO students (student_id, name, age) VALUES (1, 'Peter', 21); -- ERROR: Duplicate Primary Key

With Auto-Generated IDs

-- No need to specify student_id INSERT INTO students (name, age) VALUES ('John', 20); -- Gets ID 1 automatically INSERT INTO students (name, age) VALUES ('Mary', 19); -- Gets ID 2 automatically INSERT INTO students (name, age) VALUES ('Peter', 21); -- Gets ID 3 automatically

Much easier!

Real-World Table Examples

Students Table with Primary Key
student_id (PRIMARY KEY)nameage
1John20
2Mary19
3Peter21
3 rows
Contacts Table - Bad Example (No Primary Key)
namephone
John555-0001
John555-0001
John555-0001
3 rows
Contacts Table - Good Example (With Primary Key)
contact_id (PRIMARY KEY)namephone
1John555-0001
2Mary555-0002
3Peter555-0003
3 rows

Example 1: Online Store Products

CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(200), price DECIMAL(10, 2) );

Each product gets a unique ID.

Example 2: User Accounts

CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50), email VARCHAR(100) );

Each user gets a unique ID.

Example 3: Orders

CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_name VARCHAR(100), total DECIMAL(10, 2), order_date DATE );

Each order gets a unique ID.

What Happens Without Primary Key?

Students Table with Primary Key
student_id (PRIMARY KEY)nameage
1John20
2Mary19
3Peter21
3 rows
Contacts Table - Bad Example (No Primary Key)
namephone
John555-0001
John555-0001
John555-0001
3 rows
Contacts Table - Good Example (With Primary Key)
contact_id (PRIMARY KEY)namephone
1John555-0001
2Mary555-0002
3Peter555-0003
3 rows

Bad table (no Primary Key):

CREATE TABLE contacts ( name VARCHAR(100), phone VARCHAR(20) );

Problems:

  • Can have duplicate contacts
  • Cannot identify specific contact
  • Hard to update or delete specific row

Good table (with Primary Key):

CREATE TABLE contacts ( contact_id SERIAL PRIMARY KEY, name VARCHAR(100), phone VARCHAR(20) );

Benefits:

  • Each contact is unique
  • Easy to update: "UPDATE WHERE contact_id = 5"
  • Easy to delete: "DELETE WHERE contact_id = 5"

Common Mistakes

Students Table with Primary Key
student_id (PRIMARY KEY)nameage
1John20
2Mary19
3Peter21
3 rows
Contacts Table - Bad Example (No Primary Key)
namephone
John555-0001
John555-0001
John555-0001
3 rows
Contacts Table - Good Example (With Primary Key)
contact_id (PRIMARY KEY)namephone
1John555-0001
2Mary555-0002
3Peter555-0003
3 rows

Mistake 1: Forgetting Primary Key

Wrong:

CREATE TABLE products ( name VARCHAR(100), price DECIMAL(10, 2) );

Correct:

CREATE TABLE products ( product_id SERIAL PRIMARY KEY, name VARCHAR(100), price DECIMAL(10, 2) );

Mistake 2: Using Duplicate Values

Wrong:

INSERT INTO students (student_id, name) VALUES (1, 'John'); INSERT INTO students (student_id, name) VALUES (1, 'Mary'); -- ERROR!

Correct:

INSERT INTO students (student_id, name) VALUES (1, 'John'); INSERT INTO students (student_id, name) VALUES (2, 'Mary'); -- OK!

Mistake 3: Using NULL as Primary Key

Wrong:

INSERT INTO students (student_id, name) VALUES (NULL, 'John'); -- ERROR!

Correct:

INSERT INTO students (name) VALUES ('John'); -- Auto-generates ID

Summary

Students Table with Primary Key
student_id (PRIMARY KEY)nameage
1John20
2Mary19
3Peter21
3 rows
Contacts Table - Bad Example (No Primary Key)
namephone
John555-0001
John555-0001
John555-0001
3 rows
Contacts Table - Good Example (With Primary Key)
contact_id (PRIMARY KEY)namephone
1John555-0001
2Mary555-0002
3Peter555-0003
3 rows

Think of Primary Key as:

  • Student ID card number (unique for each student)
  • Your fingerprint (unique to you)
  • License plate number (unique for each car)
  • Social security number (unique for each person)

Key points:

  1. Primary Key uniquely identifies each row
  2. Like an ID card for each row
  3. Must be unique (no duplicates)
  4. Cannot be empty (NULL)
  5. Use SERIAL for auto-generated IDs
  6. Every table should have one
  7. Makes updating and deleting easy
  8. Prevents duplicate data

What Comes Next

Students Table with Primary Key
student_id (PRIMARY KEY)nameage
1John20
2Mary19
3Peter21
3 rows
Contacts Table - Bad Example (No Primary Key)
namephone
John555-0001
John555-0001
John555-0001
3 rows
Contacts Table - Good Example (With Primary Key)
contact_id (PRIMARY KEY)namephone
1John555-0001
2Mary555-0002
3Peter555-0003
3 rows

Now you understand Primary Keys. Next, you will learn about Foreign Keys, which connect tables together.

Finished this topic?

Mark it complete to track your progress and maintain your streak!

Previous
Aggregate Queries
Next
Foreign Key
SkillsetMaster - AI, Web Development & Data Analytics Courses