Constraints

Databases should not rely only on application logic to keep data correct.

Constraints are rules enforced by the database to ensure data accuracy, consistency, and integrity.

What Are Constraints?

A constraint is a rule applied to a table column that restricts the type of data allowed.

If data violates a constraint, the database rejects the operation.

Why Constraints Matter

Constraints help:

• Prevent invalid data entry
• Maintain data consistency
• Protect relationships between tables
• Reduce bugs at the database level

They act as a safety net for your data.

Common Types of Constraints

NOT NULL Constraint

The NOT NULL constraint ensures a column cannot have NULL values.

CREATE TABLE users (
  id INT,
  name VARCHAR(100) NOT NULL
);
  

UNIQUE Constraint

The UNIQUE constraint ensures all values in a column are different.

CREATE TABLE users (
  email VARCHAR(100) UNIQUE
);
  

PRIMARY KEY

A PRIMARY KEY uniquely identifies each row.

Key features:

• Must be UNIQUE
• Cannot be NULL
• Only one per table

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);
  

FOREIGN KEY

A FOREIGN KEY enforces relationships between two tables.

Example:

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department_id INT,
  FOREIGN KEY (department_id)
    REFERENCES departments(id)
);
  

This prevents assigning an employee to a non-existing department.

CHECK Constraint

The CHECK constraint validates values based on a condition.

CREATE TABLE employees (
  name VARCHAR(100),
  salary INT CHECK (salary > 0)
);
  

This ensures salary is always positive.

DEFAULT Constraint

The DEFAULT constraint assigns a value when none is provided.

CREATE TABLE orders (
  status VARCHAR(20) DEFAULT 'Pending'
);
  

Adding Constraints to Existing Tables

Constraints can be added using ALTER TABLE.

ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);
  

Constraints vs Application Validation

Best practice is to use both:

• Application-level validation for user experience
• Database constraints for data safety

Never rely on application logic alone.

Common Beginner Mistakes

• Forgetting PRIMARY KEY
• Not enforcing foreign keys
• Overusing CHECK constraints
• Ignoring constraint errors

Why Constraints Are Essential

Constraints ensure:

• Reliable data
• Strong relationships
• Database-level protection

They are the foundation of trustworthy databases.

What’s Next?

In the next lesson, we will explore Transactions, which allow multiple SQL operations to be executed safely as a unit.