NOT NULL and CHECK constraints

A column normally allows NULL unless it has been defined with a NOT NULL constraint.

Our products.price column is NOT NULL.

Try inserting a product without a price:

PostgreSQL rejects the insert because price is not allowed to contain NULL.

NOT NULL is useful when a value is required for every row.

For example:

means that every product must have a price.

NOT NULL tells PostgreSQL that a value must exist.

But it doesn't tell PostgreSQL which non-NULL values are valid.

For example, this value is not NULL:

but a negative product price probably doesn't make sense.

For rules like this, we can use a CHECK constraint.

Add one to the products table:

The constraint requires this expression to be satisfied:

Now try inserting a product with a negative price:

PostgreSQL rejects the insert because the value violates products_price_positive.

A simplified view looks like this:

The database now enforces the rule regardless of where the write came from.

Consider:

The two constraints enforce different rules.

NOT NULL means:

CHECK (price > 0) means:

Using both gives us the rule we actually want:

There is an important detail about CHECK constraints.

A CHECK constraint is considered satisfied when its expression evaluates to either TRUE or NULL.

For example:

does not by itself prevent:

because:

doesn't evaluate to FALSE. It evaluates to an unknown result.

If the value must also be present, use NOT NULL:

This is why NOT NULL and CHECK are often used together.

A CHECK constraint can also validate a relationship between columns in the same row.

Our orders table contains:

A reasonable rule might be that an order cannot be shipped before it was placed.

That could be expressed as:

If shipped_at is NULL, the order hasn't been shipped yet.

Otherwise, the shipping time must be equal to or later than the placement time.

This kind of rule belongs to the row as a whole rather than to one column in isolation.

A constraint that relates only to one column can often be written directly beside the column definition:

A constraint can also be written separately as a table constraint:

Table constraints are especially useful when the rule involves multiple columns.

We gave our constraint a name:

Naming constraints makes them easier to identify when PostgreSQL reports a violation and easier to modify later.

For example, we can remove it by name:

Imagine the application contains this validation:

That protects writes that go through that particular code path.

But data might also be written by:

• another API

• an admin tool

• a script

• a migration

• direct SQL

• application code containing a bug

A database constraint protects the rule regardless of which code performs the write.

Application validation is still useful because it can provide better error messages to users.

The database constraint is the final guarantee that invalid data cannot be stored.

CHECK constraints are intended to enforce conditions based on the row being inserted or updated.

For example:

or:

are appropriate because the values being checked belong to the same row.

Rules that depend on data in other rows or other tables are usually better enforced using mechanisms designed for those relationships, such as UNIQUE or foreign key constraints.

Before continuing, remove the constraint added in this chapter:

The important point is:

Constraints let PostgreSQL enforce important data rules even when application code fails to do so.