Database Constraints

Data types ensure that you don’t put text in a number column. But what if you have a number column for price, and someone inserts -50? It’s a valid integer, but it’s a terrible price!

To prevent invalid business logic from entering the database, we use Constraints. Constraints are rules applied to columns during table creation. If an INSERT or UPDATE violates a constraint, the database throws an error and rejects the change.

Common Constraints

You add constraints immediately after the data type in your CREATE TABLE statement.

1. NOT NULL

Ensures that a column cannot be left empty (NULL). name TEXT NOT NULL

2. UNIQUE

Ensures that all values in a column are entirely distinct from one another. This is critical for things like usernames or email addresses. email TEXT UNIQUE

3. DEFAULT

If an INSERT statement omits a column, this constraint provides a fallback value instead of inserting NULL. is_active BOOLEAN DEFAULT true

4. CHECK

Allows you to write a custom boolean expression that the data must satisfy. price DECIMAL(10,2) CHECK (price >= 0)

Combining Constraints

You can (and should!) chain multiple constraints together on a single column.

CREATE TABLE products (
  id INTEGER NOT NULL UNIQUE,
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
  stock_quantity INTEGER DEFAULT 0
);

By heavily constraining your database schema, you guarantee Data Integrity. Even if the application code (e.g., the Node.js backend) has a bug and tries to insert a negative price or a duplicate email, the database serves as the final, impenetrable wall of defense that rejects the bad data.

[!TIP] Always enforce data rules at the database level using constraints. Do not rely solely on your frontend forms or backend servers to validate data.

Try building a tightly constrained users table in the lab!