SQL Constraints

In a database, it is important to ensure that only valid and meaningful data is stored in tables.

SQL provides Constraints to enforce rules on the data that can be inserted or updated in a table.

Constraints help maintain:

They act as validation rules applied at the column or table level.

Why Do We Need Constraints?

Without constraints:

Constraints ensure that the database rejects incorrect data automatically.

Types of Constraints

1. NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot contain NULL values.

This means the column must always have a value.

When to Use NOT NULL?

Use NOT NULL when:

Example: NOT NULL Constraint

CREATE TABLE students (
    student_id INT,
    name VARCHAR(50) NOT NULL,
    marks INT
);

Here, the name column must have a value.

2. UNIQUE Constraint

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

It prevents duplicate values in a column.

Example: UNIQUE Constraint

CREATE TABLE users (
    user_id INT,
    email VARCHAR(100) UNIQUE
);

Here, no two users can have the same email.

Important

A table can have multiple UNIQUE constraints
UNIQUE allows one NULL value (in most databases)

3. CHECK Constraint

The CHECK constraint ensures that values in a column satisfy a specific condition.

It allows you to apply custom validation rules.

Example: CHECK Constraint

CREATE TABLE students (
    student_id INT,
    marks INT CHECK (marks >= 0 AND marks <= 100)
);

This ensures marks are between 0 and 100.

4. DEFAULT Constraint

The DEFAULT constraint provides a default value for a column when no value is specified.

Example: DEFAULT Constraint

CREATE TABLE employees (
    emp_id INT,
    status VARCHAR(20) DEFAULT 'Active'
);

If no status is provided, it will automatically be set to 'Active'.

Two Minute Drill