SQL ET POSTGRE 3.5 : Single constraints not null and check

In this video we look at the UNIQUE, NOT NULL and CHECK constraints. Each one can be applied to a specific column through the database management system. Let's start with UNIQUE. It guarantees that the values in a column are unique — no duplicates allowed. If a duplicate value is inserted, an error is raised and the insertion is refused. You declare this constraint at table creation time. A typical example: in a users table with an email column, every address must be different, so we mark the column as UNIQUE. Other columns such as first name or city should not have this constraint, since several people can share them. The id column already carries an implicit UNIQUE constraint via its primary key.

NOT NULL and CHECK

When you declare a primary key, the NOT NULL constraint is implied. This constraint ensures that no field in the column can be empty. For essential data such as an email or a last name, we apply NOT NULL so it becomes mandatory. The clearest way to see what "null" means is to look at the table: some rows have NULL in the first name or age column, meaning the person did not provide them. The insertion still works, but if we cannot accept missing emails or names, we mark those columns as NOT NULL.
  • UNIQUE: no duplicate values in the column.
  • NOT NULL: every row must provide a value.
  • CHECK: values must satisfy a boolean expression (range, format...).
age INT CHECK (age > 0)
Last, the CHECK constraint validates that values entered into a column satisfy a boolean expression. For example, an age column should contain values greater than zero, so we add CHECK (age > 0). Another example: a recipe rating must be between 1 and 5, so a CHECK keeps the value inside that range. In the next video we will create our first table using all these concepts.

Summary

This lesson covers three essential SQL table constraints: UNIQUE, NOT NULL, and CHECK. UNIQUE constraints prevent duplicate values in a column (e.g., email addresses), NOT NULL constraints ensure required fields are always populated, and CHECK constraints validate that values meet specific conditions (e.g., age greater than 0). These constraints are fundamental tools for maintaining data integrity in PostgreSQL databases.

Key points

  • UNIQUE constraint prevents duplicate values in a specified column and rejects any attempt to insert duplicate data
  • NOT NULL constraint makes a column mandatory by preventing empty or null values from being accepted
  • CHECK constraint uses boolean expressions to validate that inserted values meet specific conditions (e.g., age > 0, rating between 1-5)
  • These constraints are applied at table creation time using SQL DDL syntax
  • Primary key columns automatically have NOT NULL constraints applied
  • Column ID automatically receives UNIQUE constraint while other columns like name or city typically allow duplicates

FAQ

What is the difference between UNIQUE and NOT NULL constraints?

UNIQUE ensures no duplicate values exist in a column, while NOT NULL ensures a column always contains a value (cannot be empty). A column can be UNIQUE but allow nulls (unless explicitly NOT NULL), or NOT NULL but allow duplicates, or have both constraints applied.

How do CHECK constraints work in PostgreSQL?

CHECK constraints use boolean expressions to validate data. For example, `CHECK (age > 0)` ensures age values must be positive, and `CHECK (rating BETWEEN 1 AND 5)` ensures rating values fall within that range. Any insert or update violating the CHECK condition is rejected.

Can multiple columns have the UNIQUE constraint?

Yes, multiple columns can each have UNIQUE constraints individually. However, not all columns should have UNIQUE constraints—only those where duplicates don't make sense, such as email addresses or ID fields. Columns like first name or city typically allow duplicate values.