🔐 SQL Constraints: NOT NULL, UNIQUE, CHECK, and DEFAULT Explained

In SQL, constraints are used to enforce rules on the data in your tables. In this tutorial, we’ll cover four essential constraints: NOT NULL, UNIQUE, CHECK, and DEFAULT — with examples that are simple and practical.

✅ 1. NOT NULL – Make Sure a Value is Provided

NOT NULL means a column cannot have an empty (NULL) value. It ensures that data is always present.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100)
);

Here, username must be filled in. If you try to insert a row without it, the database will throw an error.

🔁 2. UNIQUE – Prevent Duplicate Entries

UNIQUE ensures that all values in a column are different — no duplicates allowed.

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE
);

You cannot insert another employee with the same email.

🛡️ 3. CHECK – Add Conditions to Your Data

CHECK allows you to define a condition that the data must meet before it's inserted or updated.

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  price DECIMAL(10,2),
  quantity INT CHECK (quantity >= 0)
);

This ensures that quantity can never be negative.

More examples:

age INT CHECK (age BETWEEN 18 AND 60)

This allows only ages between 18 and 60.

🎯 4. DEFAULT – Auto-Fill a Value

DEFAULT provides an automatic value if one is not supplied during data insertion.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  status VARCHAR(20) DEFAULT 'Pending'
);

If you insert an order without specifying status, it will automatically be set to "Pending".

📋 Combined Example

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  age INT CHECK (age BETWEEN 18 AND 35),
  country VARCHAR(30) DEFAULT 'Bangladesh'
);

This table ensures:

name is required
email is unique
age is between 18 and 35
country is automatically set to "Bangladesh" if not provided

🧠 Summary Table

Constraint	Purpose	Example
NOT NULL	Ensures the column is never empty	`name VARCHAR(50) NOT NULL`
UNIQUE	Prevents duplicate values	`email VARCHAR(100) UNIQUE`
CHECK	Validates values against a condition	`age INT CHECK (age >= 18)`
DEFAULT	Assigns a value if none is given	`status VARCHAR(20) DEFAULT 'New'`

🎯 Final Thoughts

These constraints help enforce rules and protect the integrity of your database. Use them wisely in your table designs to prevent bad data and reduce errors in your applications.

Trust Coding : Programming

🛡️ NOT NULL, UNIQUE, CHECK, and DEFAULT in SQL Explained