SQL Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY, FOREIGN KEY

In SQL, constraints are rules applied to table columns to ensure valid and consistent data. They help maintain accuracy and integrity in the database.

1. NOT NULL Constraint

Purpose:
Ensures that a column cannot have a NULL (empty) value.

Use Case:
Useful for fields like name, email, or employee_id that must always have data.

Example:

2. UNIQUE Constraint

Purpose:
Ensures that all values in a column (or group of columns) are different.

Use Case:
Often used for fields like email, phone number, or username where duplication isn’t allowed.

Example:

A table can have multiple UNIQUE constraints, but only one PRIMARY KEY.

3. CHECK Constraint

Purpose:
Validates that values in a column satisfy a specific condition.

Use Case:
Helps enforce business rules like valid age range, salary limits, etc.

Example:

4. DEFAULT Constraint

Purpose:
Sets a default value for a column if no value is provided during insertion.

Use Case:
Useful for status flags, timestamps, or roles.

Example:

5. PRIMARY KEY Constraint

Purpose:
Uniquely identifies each record in the table and ensures that the column is both NOT NULL and UNIQUE.

Use Case:
Essential for referencing rows and establishing relationships between tables.

Example:

Each table can have only one primary key, which can consist of one or more columns (composite key).

6. FOREIGN KEY Constraint

Purpose:
Creates a link between two tables by enforcing that a column’s values match the PRIMARY KEY of another table.

Use Case:
Maintains referential integrity by ensuring that related data exists.

Example:

If you try to insert a CustomerID in Orders that doesn’t exist in Customers, the database will reject it.

Summary Table