PRIMARY KEY and NOT NULL

Objectives

By the end of this lesson, you should be able to:

• Explain what PRIMARY KEY guarantees
• Explain what NOT NULL guarantees
• Predict when an INSERT will be rejected by either constraint

💡 Why this matters: These are the two most common constraints in real schemas. PRIMARY KEY makes every row identifiable, NOT NULL makes sure the columns that matter can’t quietly go missing.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

PRIMARY KEY: Uniquely Identifying a Row

id SERIAL PRIMARY KEY

PRIMARY KEY marks a column as the unique identifier for every row, no two rows can share the same value, and it can never be NULL. A table can only have one primary key (though it can span more than one column, covered in Module 9).

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

Given id = 2, there’s exactly one row that could match, no ambiguity. This is the same surrogate-key idea, a plain integer with no meaning beyond identifying the row, that every table in this course uses.

NOT NULL: This Column Can’t Be Empty

first_name VARCHAR(50) NOT NULL

Every row must have a real value here, NULL isn’t allowed. Trying to insert one without it fails:

INSERT INTO employees (first_name, last_name, email, salary)
VALUES (NULL, 'Test', 'test@example.com', 50000);

ERROR: null value in column "first_name" of relation "employees" violates not-null constraint

Combining Them

Most tables use both together, PRIMARY KEY on the identifying column, NOT NULL on whichever other columns genuinely can’t be missing:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10,2) NOT NULL
);

department deliberately has no NOT NULL, from the last lesson, not every employee has one recorded yet, and that’s fine. first_name, last_name, and salary are all required, an employee row without them wouldn’t make sense.

Try It

1. Explain, in one sentence each, what PRIMARY KEY and NOT NULL each guarantee.
2. Write a CREATE TABLE statement for a departments table with a PRIMARY KEY id and a NOT NULL name.
3. Write an INSERT statement that would violate the NOT NULL constraint on name, and explain what error you’d expect.
4. Explain why a table can only have one PRIMARY KEY, but can have many NOT NULL columns.

Recap

• PRIMARY KEY guarantees a column uniquely identifies every row, and is never NULL.
• NOT NULL guarantees a column always has a real value, rejecting any INSERT or UPDATE that would leave it empty.
• A table has exactly one primary key, but as many NOT NULL columns as make sense for the data.

Next lesson: UNIQUE, for columns that need distinct values without being the table’s primary key.