Adding Constraints When Creating Tables: NOT NULL, UNIQUE

You can have a ton of data, but not all of it is useful. To keep your table from turning into a dumpster fire, PostgreSQL lets you set up constraints. Think of them as simple rules so nobody writes numbers in the "name" field or leaves it empty.

What are constraints?

Constraints in PostgreSQL are like rules in the army. If you don’t have them, anyone can do whatever they want: set a user’s age to -999, add an empty string instead of a name, or record the same student twice under different ids. To prevent this, PostgreSQL gives you control mechanisms called constraints.

Constraints let you:

1. Keep your data valid and consistent.
2. Make sure your data follows certain rules (like, a person’s age can’t be negative).
3. Find errors faster if your data isn’t what you expect.

Today we’ll check out the two most common constraints: NOT NULL and UNIQUE.

The NOT NULL Constraint

The NOT NULL constraint tells PostgreSQL: "Hey, this field always needs a value. No uncertainty allowed here!". If you try to add a row with NULL in this field, PostgreSQL will throw an error. This is super useful for fields that are required, like a student’s name or date of birth.

Example of NOT NULL Usage

CREATE TABLE teachers (
    id SERIAL PRIMARY KEY,          -- Unique identifier
    name VARCHAR(100) NOT NULL,     -- Teacher's name (required)
    subject VARCHAR(50) NOT NULL    -- Subject they teach (required)
);

In this example, the name and subject columns can’t be left empty. If you try to insert a row without values for these columns, you’ll get an error.

INSERT INTO teachers (name, subject)
VALUES (NULL, 'Mathematics');

Result: error! You didn’t provide the teacher’s name, and name needs a value.

The UNIQUE Constraint: Uniqueness Required

The UNIQUE constraint makes sure that values in a column or a combo of columns don’t repeat. This is handy if you want to make sure two students don’t have the same student book number.

If NOT NULL is like saying "everyone at the party has to be dressed," then UNIQUE is "no one can wear the same dress."

Example of UNIQUE Usage

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,      -- Unique identifier
    name VARCHAR(100) UNIQUE    -- Department name must be unique
);

Now you can’t add two departments with the same name.

INSERT INTO departments (name) VALUES ('Mathematics');
INSERT INTO departments (name) VALUES ('Mathematics');

Result: error! You tried to insert a row with a duplicate value in the name column.

Combined UNIQUE Constraint

You can also set uniqueness for a combo of two (or more) columns. For example:

CREATE TABLE enrollments (
    id SERIAL PRIMARY KEY,          -- Unique identifier for the record
    student_id INTEGER NOT NULL,    -- Student ID
    course_id INTEGER NOT NULL,     -- Course ID
    UNIQUE (student_id, course_id)  -- A student can’t enroll in the same course twice
);

Now every student_id and course_id pair has to be unique. If someone tries to enroll the same student in the same course twice, PostgreSQL won’t let it happen.

Combining Constraints: NOT NULL and UNIQUE

You can combine NOT NULL and UNIQUE for a single column. For example:

CREATE TABLE users (
    username VARCHAR(50) NOT NULL UNIQUE, -- Unique login, required
    email VARCHAR(100) NOT NULL UNIQUE -- Unique email address, required
);

Here, both the login and email must be unique and can’t be empty.

Common Mistakes When Using Constraints

If you use UNIQUE on a column but don’t add NOT NULL, you can insert multiple rows with NULL values, since PostgreSQL thinks NULL isn’t equal to another NULL. If you want to block empty values, add NOT NULL too.

Trying to add a duplicate value to a column with a UNIQUE constraint will throw an error. Always check your input before running INSERT.

Using too many constraints can slow down insert operations.

Why Bother With All This?

Using constraints helps you avoid a bunch of problems, like duplicate data, empty strings, and other mistakes that happen because people mess up. They make your database "self-protecting," and for big projects, that saves a ton of time. Interviewers love to ask about constraints, so knowing NOT NULL and UNIQUE isn’t just a useful skill — it’s a way to impress future employers.

In the next lecture, we’ll talk about the DEFAULT constraint, which lets you set default values for columns if the user doesn’t provide anything. See you then!

2

Task

SQL SELF, level 17, lesson 1

Locked

Creating a table with a `NOT NULL` constraint

Creating a table with a `NOT NULL` constraint

Open

2

Task

SQL SELF, level 17, lesson 1

Locked

Creating a table with a `UNIQUE` constraint

Creating a table with a `UNIQUE` constraint

Open