Back to course

Data Integrity Constraints (NOT NULL, UNIQUE, CHECK)

Database Fundamentals: From Zero to Hero

Lesson 12: Data Integrity Constraints (NOT NULL, UNIQUE, CHECK)

Constraints are rules applied to columns to limit what data can be stored, ensuring the reliability and quality of your database.

1. NOT NULL Constraint

This constraint ensures that a column cannot contain missing values (NULL). If you attempt to insert a record without providing a value for a NOT NULL column, the operation fails.

  • Use Case: Critical data like a user's login ID or a product's name.

sql CREATE TABLE Users ( UserID INTEGER PRIMARY KEY, Username VARCHAR(50) NOT NULL, -- Must have a username LastLogin DATE );

2. UNIQUE Constraint

This constraint ensures that all values in a column (or group of columns) are unique. Unlike the Primary Key, a table can have multiple UNIQUE constraints, and the column may still allow NULL values (though only one NULL entry is usually permitted).

  • Use Case: Email addresses, serial numbers, passport IDs.

sql CREATE TABLE Users ( UserID INTEGER PRIMARY KEY, Email VARCHAR(100) UNIQUE -- Email must be unique among all users );

3. CHECK Constraint

This constraint defines a specific condition that must be met for every value inserted into a column.

  • Use Case: Ensuring that ages are positive, or salaries are above minimum wage.

sql CREATE TABLE Products ( ProductID INTEGER PRIMARY KEY, Price DECIMAL(10, 2),

-- Price must always be greater than zero
CHECK (Price > 0)

);