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

When designing a database, it’s not enough to just define tables and columns—you must also ensure that the data entered into those tables is accurate, consistent, and reliable. This is where SQL constraints come in.

SQL constraints are rules you apply to columns or tables that restrict the type of data that can be inserted. These constraints help enforce data integrity, which is the accuracy and consistency of data over its lifecycle.

In this guide, we’ll explain the most important SQL constraints:

• PRIMARY KEY
• FOREIGN KEY
• NOT NULL
• UNIQUE
• CHECK
• DEFAULT

Each will be covered in detail with examples so that even if you’re new to SQL, you’ll understand how to use them effectively.

🔐 1. PRIMARY KEY

The PRIMARY KEY is one of the most important constraints in SQL. It uniquely identifies each record in a table.

✅ Key Features:

• Must be unique
• Cannot be NULL
• Only one primary key per table (can be single or composite)

📌 Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(50)
);

In this example, EmployeeID is the primary key, meaning no two employees can have the same ID, and the field cannot be left empty.

🔗 2. FOREIGN KEY

A FOREIGN KEY is used to create a relationship between two tables. It points to a primary key in another table, ensuring referential integrity.

✅ Key Features:

• Enforces consistency between linked tables
• Helps avoid orphan records
• Can be used for cascading actions (e.g., ON DELETE CASCADE)

📌 Example:

CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

Here, DeptID in the Employees table must match a valid DeptID in the Departments table.

❌ 3. NOT NULL

The NOT NULL constraint ensures that a column cannot have a NULL value. It’s used when a field must always contain a value.

✅ Key Features:

• Prevents blank entries
• Commonly used in essential fields like emails or usernames

📌 Example:

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100) NOT NULL
);

Here, both Username and Email must be provided for each user.

🌟 4. UNIQUE

The UNIQUE constraint ensures that all values in a column are different. It allows NULL values unless combined with NOT NULL.

✅ Key Features:

• Can be applied to multiple columns
• Enforces uniqueness without being a primary key

📌 Example:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);

Now, no two customers can have the same email address.

You can also apply UNIQUE to more than one column:

CREATE TABLE Orders (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    UNIQUE(OrderID, ProductID)
);

This ensures that the same product isn’t listed twice in the same order.

🧪 5. CHECK

The CHECK constraint allows you to set conditions for the values in a column. It’s used for simple validations like number ranges or specific allowed values.

✅ Key Features:

• Filters data based on a logical condition
• Helps avoid invalid or out-of-range inputs

📌 Example:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT CHECK (Age >= 5 AND Age <= 100)
);

This restricts the Age column to values between 5 and 100.

You can also use it for string-based conditions:

CHECK (Gender IN ('Male', 'Female', 'Other'))

🧰 6. DEFAULT

The DEFAULT constraint automatically assigns a default value to a column when no value is provided during insertion.

✅ Key Features:

• Reduces manual input
• Useful for flags, timestamps, statuses, etc.

📌 Example:

CREATE TABLE Tasks (
    TaskID INT PRIMARY KEY,
    TaskName VARCHAR(100),
    Status VARCHAR(20) DEFAULT 'Pending'
);

If you insert a task without specifying a status, SQL will automatically use 'Pending'.

INSERT INTO Tasks (TaskID, TaskName)
VALUES (1, 'Write SQL Article');
-- Status = 'Pending'

🔄 Combining Multiple Constraints

You can apply multiple constraints to a single column or across several columns.

📌 Example:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL UNIQUE,
    Price DECIMAL(10, 2) CHECK (Price > 0),
    InStock BOOLEAN DEFAULT TRUE
);

This table uses:

• PRIMARY KEY for unique product IDs
• NOT NULL and UNIQUE for product names
• CHECK to ensure price is positive
• DEFAULT to assume the product is in stock unless specified otherwise

🚨 Common Mistakes to Avoid

• Using NULL when NOT NULL is more appropriate
• Forgetting FOREIGN KEY relationships, leading to inconsistent data
• Not using CHECK for data validation, allowing incorrect inputs
• Relying only on the application layer for constraints (always validate at the database level too)

📘 Summary Table of SQL Constraints

🏁 Final Thoughts

Understanding SQL constraints is fundamental for building strong and reliable databases. Constraints make sure that:

• Your data stays accurate
• Relationships between tables are maintained
• Bad or incomplete data is rejected automatically

By thoughtfully using constraints like PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK, and DEFAULT, you lay the foundation for a clean, well-structured, and scalable database.