Most developers think they understand database constraints — until PostgreSQL politely corrects them. Constraints aren’t just rules. They’re guarantees. They tell PostgreSQL what must be true at all times. And when constraints are designed well, your data stays clean and your application becomes dramatically safer.

In this guide, we’ll walk through every major PostgreSQL constraint:

• Primary Keys
• Unique Constraints
• Foreign Keys
• Check Constraints
• Deferrable Constraints

And we’ll use the Pagila Sample Database to show real-world examples of how they behave.

Primary Keys: The Identity of a Row

A primary key uniquely identifies a row in a table. It’s the table’s identity. PostgreSQL enforces two rules automatically:

• The value must be unique
• The value must be NOT NULL

In Pagila, the customer table uses customer_id as its primary key:

\d+ customer

PostgreSQL backs primary keys with a unique btree index, so lookups by the primary key are always fast.

Important: You don’t need a serial or identity column — any unique, non-nullable value works. But 99% of the time, a simple numeric primary key is the right choice.

Unique Constraints: Guaranteeing No Duplicates

A UNIQUE constraint prevents duplicates for a column or group of columns. Unlike a primary key, the column can be NULL. In fact, multiple NULLs are allowed because NULL is treated as “unknown,” not equal.

In Pagila, the staff table contains a duplicate username. Before adding a unique constraint, the data must be cleaned. You locate and resolve the duplicate like this:

select * from staff where username in (
select username
from staff
group by username 
having count(*) > 1
);

UPDATE staff
SET username = username || '_2'
WHERE staff_id = 270;

Once the duplicate is resolved, the constraint can be added:

ALTER TABLE staff
ADD CONSTRAINT staff_username_key UNIQUE (username);

PostgreSQL enforces this by creating a unique btree index behind the scenes.

Composite Unique Constraints

A unique constraint can span multiple columns, such as preventing the same customer from renting the same film twice at the same time.

ALTER TABLE rental
ADD CONSTRAINT unique_customer_rental
  UNIQUE (customer_id, inventory_id, rental_date);

This is common in many-to-many join tables and prevents sneaky data anomalies that look harmless until you get that “Why is this row duplicated?” Slack message.

Foreign Keys: Protecting Relationships

Foreign keys are the backbone of relational data integrity. They ensure that every reference in a child table actually exists in the parent table. PostgreSQL enforces these rules automatically, preventing orphaned rows and keeping relationships consistent across your entire schema.

In Pagila, a great example of foreign key usage is the customer table. Every customer must belong to a valid store and must have a valid address.

\d customer

PostgreSQL enforces this with two foreign key constraints:

• address_id → address(address_id)
• store_id → store(store_id)

This means you cannot insert a customer with a nonexistent address or store, and you cannot delete an address if customers depend on it. PostgreSQL prevents accidental data corruption long before it reaches your application.

The rental table is another strong example. Every rental must reference a valid customer, inventory item, and staff member.

\d rental

These foreign keys ensure that:

• You cannot rent an item to a nonexistent customer
• You cannot delete an inventory row while rentals exist for it
• Staff changes cascade correctly across related rows

This is where performance meets correctness. Foreign keys don’t just protect your data — they give the optimizer better information, helping PostgreSQL build safer, more efficient execution plans.

PostgreSQL also gives you control over what happens when a parent row is deleted. These are known as foreign key actions, and they determine how the database handles dependent rows:

• ON DELETE RESTRICT – default; prevents deleting parent rows
• ON DELETE CASCADE – deleting a customer deletes dependent rows
• ON DELETE SET NULL – clears the child reference
• ON DELETE SET DEFAULT – resets to a default value

Pagila generally uses RESTRICT, which is why you can’t delete a rental if payments reference it.

DELETE FROM rental WHERE rental_id = 1;
-- ERROR: update or delete on table "rental" violates foreign key constraint

That’s PostgreSQL doing its job — protecting your data from becoming garbage.

Check Constraints: Enforcing Business Logic

CHECK constraints enforce custom rules that must always be true. They’re incredibly powerful. If you’ve ever said “Our application should never allow X,” you probably want a CHECK constraint.

Example 1: Ensuring rental duration is positive.

ALTER TABLE film
ADD CONSTRAINT check_rental_duration
  CHECK (rental_duration > 0);

Example 2: Ensuring MPAA ratings are valid.

ALTER TABLE film
ADD CONSTRAINT check_mpaa_rating
  CHECK (rating IN ('G', 'PG', 'PG-13', 'R', 'NC-17'));

Now PostgreSQL enforces this across all clients, all microservices, all languages — forever. No code drift, no bugs, no “we forgot to validate that in the new API” problems. PostgreSQL isn't just a database — it’s a rule engine!

Deferrable Constraints: The Secret Weapon

Most developers never use this feature — and they’re missing out.

By default, PostgreSQL checks constraints immediately after each row modification. But you can make constraints DEFERRABLE, meaning they are only checked at the end of the transaction.

This is extremely useful for:

• Bulk updates
• Circular foreign key dependencies
• Re-syncing bad data
• ETL operations

Let’s look at a real example from the Pagila database. The address table has a foreign key to city. Normally, PostgreSQL would prevent you from setting an address to a city that doesn’t exist. But by making the constraint deferrable, you can temporarily violate it inside a transaction — as long as it’s fixed before COMMIT.

ALTER TABLE address
DROP CONSTRAINT address_city_id_fkey;

ALTER TABLE address
ADD CONSTRAINT address_city_id_fkey
  FOREIGN KEY (city_id)
  REFERENCES city(city_id)
  DEFERRABLE INITIALLY DEFERRED;

Now we can do the impossible: temporarily point an address to a city that doesn’t exist yet, then create that city before committing the transaction.

BEGIN;

-- Temporarily invalid: city_id 99999 does not exist yet
UPDATE address
SET city_id = 99999
WHERE address_id = 1;

-- Fix the violation inside the same transaction
INSERT INTO city (city_id, city, country_id)
VALUES (99999, 'New Demo City', 1);

COMMIT;

Without a deferrable constraint, the UPDATE would fail instantly. But with one, PostgreSQL calmly waits until COMMIT to validate the relationship — and everything passes because the data is consistent by the end of the transaction.

Conclusion

Constraints are the foundation of trustworthy data. They protect your application from yourself, your teammates, and every bug that hasn’t been discovered yet.

Whether you're defining a simple primary key or orchestrating a multi-table transactional update with deferrable constraints, PostgreSQL gives you the tools to ensure your data stays clean and correct.

Treat constraints not as restrictions, but as guarantees your database proudly enforces. The more you use them, the more reliable your entire system becomes!