Skip to main content
PostgreSQL
CHAPTER 12 Intermediate

Primary Keys, Unique Keys, and Sequences

Updated: May 16, 2026
6 min read

# CHAPTER 12

Primary Keys, Unique Keys, and Sequences

1. Introduction

Before we learn how to stitch multiple tables together using JOINs, we must ensure our tables are mathematically sound. In a relational database, every single row must be distinctly identifiable. If a database contains two users named "John Smith" who live at the same address, the system cannot reliably update or delete the correct John. In this chapter, we will master Keys and Constraints—the strict architectural rules that guarantee data uniqueness and absolute integrity.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Understand the critical role of a PRIMARY KEY.
  • Define a table using single and composite Primary Keys.
  • Enforce data uniqueness using the UNIQUE constraint.
  • Understand how PostgreSQL uses SEQUENCE objects behind the scenes.
  • Differentiate between SERIAL and modern identity columns.

3. The PRIMARY KEY Constraint

A Primary Key is a specific column (or group of columns) designed to uniquely identify every row in a table. The Primary Key has two absolute mathematical rules:
  1. 1. It must be UNIQUE. (No two rows can have the same key).
  1. 2. It cannot be NULL. (Every row MUST have a key).
sql
123456 
CREATE TABLE users (
    -- The id is our guaranteed unique identifier
    user_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

4. The UNIQUE Constraint

Sometimes, a column isn't the Primary Key, but it still absolutely must be unique. For example, two users can have the name "John", but two users CANNOT register with the same email_address.

We apply the UNIQUE constraint to enforce this rule at the database level.

sql
12345 
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(150) UNIQUE, -- The database will block duplicate emails!
    username VARCHAR(50) UNIQUE
);

*(If a PHP application tries to INSERT an email that already exists, PostgreSQL will reject it and throw a fatal error, protecting your data).*

5. Composite Primary Keys

A Primary Key doesn't have to be just one column. Sometimes, uniqueness is defined by the *combination* of two columns. This is called a Composite Key, and it is heavily used in "Pivot" tables (which we will cover in Chapter 13).
sql
1234567 
CREATE TABLE student_classes (
    student_id INTEGER,
    class_id INTEGER,
    enrollment_date DATE,
    -- The combination of Student + Class is the unique identifier
    PRIMARY KEY (student_id, class_id) 
);

*(This ensures Student #5 can only enroll in Class #10 exactly one time. They cannot be enrolled in the same class twice).*

6. Demystifying SERIAL and SEQUENCE

In MySQL, you use AUTO_INCREMENT. In PostgreSQL, we have used SERIAL. But what is SERIAL actually doing? SERIAL is not a real data type. It is syntactic sugar. When you type SERIAL, PostgreSQL does three things behind the scenes:
  1. 1. It creates an INTEGER column.
  1. 2. It creates an independent database object called a SEQUENCE. (A Sequence is basically a small counter program that generates sequential numbers: 1, 2, 3...).
  1. 3. It sets the default value of the column to automatically pull the next number from that Sequence.

7. Modern PostgreSQL: GENERATED ALWAYS AS IDENTITY

While SERIAL is still widely used, modern PostgreSQL (Version 10+) introduced a new, SQL-standard way to create auto-incrementing primary keys that is safer and stricter.
sql
12345 
-- The Modern standard way to write an auto-incrementing PK
CREATE TABLE products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100)
);

Why is this better? With SERIAL, a developer can accidentally write INSERT INTO products (product_id) VALUES (5); and mess up the sequence counter. With GENERATED ALWAYS AS IDENTITY, PostgreSQL strictly blocks developers from manually inserting ID numbers, guaranteeing the sequence never breaks!

8. Mini Project: Hardened Registration Schema

Let's build a registration table that is mathematically bulletproof.
sql
123456 
CREATE TABLE registrations (
    reg_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(200) UNIQUE NOT NULL,
    license_number VARCHAR(50) UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

9. Common Mistakes

  • Using Natural Data as Primary Keys: Never use an Email, a Phone Number, or a Social Security Number as a Primary Key. Users change their emails. If the email is the Primary Key, changing it will break the links to every other table in the database. Always use a meaningless, auto-generated ID (like user_id = 5).
  • Forgetting NOT NULL on Unique columns: A UNIQUE column allows multiple NULL values by default! If you want an email to be unique AND required, you must write email VARCHAR UNIQUE NOT NULL.

10. Best Practices

  • UUIDs for Public APIs: If you are building a modern REST API or mobile app, consider using UUID instead of integers for your Primary Key. This prevents hackers from scraping your API by simply looping through IDs 1, 2, 3, 4.

11. Exercises

  1. 1. What two strict conditions does a PRIMARY KEY enforce on a column?
  1. 2. Write the DDL command to create a categories table using the modern GENERATED ALWAYS AS IDENTITY syntax for the primary key.

12. SQL Challenges

Write the DDL syntax to create a table named employee_devices. An employee can have multiple devices, but they cannot register the same device_mac_address twice. Make a composite primary key out of emp_id and device_mac_address.
sql
123456 
CREATE TABLE employee_devices (
    emp_id INTEGER,
    device_mac_address VARCHAR(50),
    assigned_date DATE,
    PRIMARY KEY (emp_id, device_mac_address)
);

13. MCQ Quiz with Answers

Question 1

What happens if a backend application attempts to INSERT a new row containing an email address that already exists in a column marked with the UNIQUE constraint?

Question 2

What is the modern, SQL-standard replacement for the PostgreSQL SERIAL keyword?

14. Interview Questions

  • Q: Contrast the PostgreSQL SERIAL pseudo-type with the modern GENERATED ALWAYS AS IDENTITY syntax. Why is the latter considered safer for enterprise databases?
  • Q: Define a Composite Primary Key and explain a specific database modeling scenario where it is absolutely required.

15. FAQs

Q: Can a table have multiple Primary Keys? A: No. A table can have exactly ONE Primary Key. However, as we learned, that single Primary Key can be a *Composite Key* made up of multiple columns.

16. Summary

Data integrity is the paramount responsibility of a database. By utilizing Primary Keys to identify rows and Unique constraints to prevent logical duplicates, you ensure your tables contain pristine, reliable data. With SEQUENCES and IDENTITY columns, PostgreSQL handles the heavy lifting of ID generation automatically.

17. Next Chapter Recommendation

Now that our tables possess unshakeable unique identifiers, we can finally begin linking them together. In Chapter 13: Foreign Keys and Table Relationships, we will master the mathematical bonds that define the true power of a "Relational" Database.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter GROUP BY and HAVING Clauses
Next Chapter Foreign Keys and Table Relationships

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6