🔍 What are Constraints in SQL?

In any database, maintaining accuracy and consistency of data is essential. This is where constraints in SQL come into play. A constraint is simply a rule that defines what kind of data can be stored in a table, ensuring data integrity and reliability. Without these rules, databases could quickly fill up with incorrect, duplicate, or missing values.

In this guide, we’ll explain the types of SQL constraints, why they matter, and how to use them with practical examples. Whether you’re a beginner or a professional, mastering constraints will help you design robust, error-free databases.

📘 Definition of SQL Constraints

A SQL Constraint is a rule that limits the type of data that can be stored in a column or table. In simple words, it ensures that the database always follows valid, consistent, and accurate rules.

🧩 Types of SQL Constraints (With Examples)

1) NOT NULL

Ensures a column cannot have empty values.


CREATE TABLE students (
  id INT,
  name VARCHAR(100) NOT NULL
);

2) UNIQUE

Ensures all values in a column are distinct (no duplicates).


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

3) PRIMARY KEY

Uniquely identifies each row in the table; automatically NOT NULL and UNIQUE.


CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2)
);

4) FOREIGN KEY

Maintains referential integrity by linking a column to the primary key of another table.


CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT,
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

5) CHECK

Validates values against a condition or rule.


CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  age INT CHECK (age >= 18)
);

6) DEFAULT

Assigns a default value to a column when no value is provided.


CREATE TABLE accounts (
  acct_id INT PRIMARY KEY,
  balance DECIMAL(10,2) DEFAULT 0.00,
  status VARCHAR(20) DEFAULT 'active'
);

⚡ Why Are SQL Constraints Important?

SQL constraints are not just database rules—they are guardrails that keep your data accurate, secure, and meaningful. Without constraints, a database can quickly become inconsistent, error-prone, and unreliable.

✔ Ensures Data Integrity

PRIMARY KEY and FOREIGN KEY constraints prevent duplicates and maintain valid relationships between tables.

✔ Enforces Business Rules

Constraints like CHECK and NOT NULL guarantee that only valid, business-approved values are stored in your database.

✔ Improves Query Performance

Unique indexes created by PRIMARY and UNIQUE constraints help databases run queries faster and more efficiently.

✔ Reduces Human Errors

By automatically rejecting invalid or missing data, constraints minimize mistakes and ensure clean, trustworthy records.

🚀 Key Takeaway

SQL constraints act as safety nets in database design. They protect data accuracy, enforce rules, and make your applications faster, smarter, and more reliable.

Primary Key Constraint

A Primary Key uniquely identifies each row in a table. It guarantees uniqueness and disallows NULL values—critical for maintaining data integrity and powering fast joins.

Key Points

  • Uniqueness: Each row must have a distinct value in the primary key column.
  • NOT NULL: The primary key column cannot contain NULL values.
  • Identification: Often used as CustomerID, OrderID, etc., to pinpoint records.
  • Performance: Speeds up lookups and JOIN operations across related tables.

Example of Primary Key in SQL 

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

Here, CustomerID is the primary key—every customer has a unique, non-null identifier.

Primary Key Constraints: Essential for Database Integrity

The PRIMARY KEY keeps rows unique and identifiable—forming the backbone of reliable relational design.

Foreign Key Constraint

A Foreign Key creates a relationship between two tables by referencing the primary key of another table. It enforces referential integrity, ensuring that related data always stays consistent.

Key Points

  • Referential Integrity: Ensures values in the foreign key column exist in the parent table’s primary key column.
  • Relationship Enforcement: Defines real-world links such as “a customer places an order” or “a student enrolls in a course.”
  • Handling Orphans: Options like ON DELETE CASCADE or ON UPDATE CASCADE manage related records automatically.
  • Multiple Relationships: A table can have multiple foreign keys to link with multiple parent tables.

Example of Foreign Key in SQL 

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

In this example, the CustomerID in Orders is a foreign key referencing Customers.CustomerID. This ensures every order is tied to a valid customer.

Foreign Key Constraints: Safeguarding Relationships

The FOREIGN KEY guarantees consistency across tables, ensuring data always reflects real-world relationships without orphaned records.

Unique Constraint in SQL (With Example & Use Cases)

The Unique Constraint in SQL ensures that all values in a column are distinct. It is often applied to fields such as email addresses, usernames, or product codes to prevent duplicates and maintain data integrity.

🔑 Key Points About SQL Unique Constraint:

  • Ensures Uniqueness: No duplicate values are allowed in the column.
  • Multiple Constraints: A table can have multiple UNIQUE columns.
  • NULL Values: A unique column can contain a single NULL value.
  • Use Cases: Common for emails, usernames, and identifiers.

Example of Unique Constraint in SQL: 

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(100) UNIQUE,
    Email VARCHAR(100) UNIQUE
);

Here, both Username and Email are marked as UNIQUE. This prevents duplicate accounts and maintains reliable database integrity.

✅ Why Unique Constraints Matter in SQL

By enforcing uniqueness in critical fields, the UNIQUE constraint in SQL eliminates duplicate data, prevents user conflicts, and ensures reliable application performance.

NOT NULL Constraint in SQL (Definition, Example & Use Cases)

The SQL NOT NULL constraint ensures that a column cannot have a NULL value. It enforces mandatory fields, making sure every record has valid and complete data. Commonly used for usernames, product names, and order statuses.

🔑 Key Highlights of NOT NULL:

  • Mandatory Data: Ensures no field is left empty.
  • Data Integrity: Prevents missing or incomplete data entries.
  • Use Cases: Ideal for names, statuses, and other required values.

✅ Example: NOT NULL Constraint in SQL 

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    ProductName VARCHAR(255) NOT NULL,
    OrderStatus VARCHAR(100) NOT NULL
);

Here, both ProductName and OrderStatus are NOT NULL. This ensures every order has complete, valid information.

CHECK Constraint in SQL (Definition, Example & Use Cases)

The SQL CHECK constraint restricts values in a column to meet specific conditions. It enforces business rules like ensuring salaries are above minimum wage, prices are positive, or age is within a valid range.

🔎 Key Highlights of CHECK:

  • Validation: Ensures only valid data values are inserted.
  • Custom Rules: Can enforce logical conditions (>, <, AND, OR).
  • Use Cases: Salary validation, product price restrictions, minimum quantity checks.

✅ Example: CHECK Constraint in SQL 

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2) CHECK (Price > 0)
);

Here, the Price column must always be greater than 0, preventing invalid or negative values from being entered.

🚀 Why NOT NULL and CHECK Constraints Are Essential

NOT NULL guarantees required fields are always filled, while CHECK enforces valid values based on business rules. Together, they protect your SQL database from incomplete or invalid data.

CHECK Constraint in SQL (Definition, Example & Use Cases)

The CHECK constraint in SQL ensures that values stored in a column (or across columns) satisfy a rule. It’s ideal for enforcing business logic like age limits, positive prices, or valid ranges.

🔎 Key Points

  • Data Validation: Accepts only values that meet specified criteria.
  • Business Rules: Enforce policies such as minimum age, positive quantity, or valid ranges.
  • Flexible Logic: Supports conditions with AND, OR, and comparison operators.

✅ Example: CHECK Constraint in SQL

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name       VARCHAR(100),
    Age        INT CHECK (Age > 18),
    Position   VARCHAR(100)
);

Here, Age must be greater than 18. Any row that violates this rule is rejected by the database engine.

Why the CHECK Constraint Matters

By validating values at the database layer, the CHECK constraint prevents invalid or inconsistent data, strengthens data integrity, and keeps application logic clean and reliable.

DEFAULT Constraint in SQL (Definition & Example)

The DEFAULT constraint in SQL automatically assigns a predefined value to a column when no value is provided during insertion. This ensures that important fields are never left blank and your database maintains consistency.

🔑 Key Points of the DEFAULT Constraint

  • Automatic Assignment: Fills in a default value if none is provided during INSERT.
  • Error Prevention: Reduces the chances of incomplete or missing data.
  • Common Use Cases: Useful for status, timestamps, and default numeric values.

✅ Example of DEFAULT Constraint in SQL 

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    UserName VARCHAR(100),
    AccountStatus VARCHAR(50) DEFAULT 'Active'
);

Here, if AccountStatus is not specified while inserting a record, SQL automatically assigns it the default value 'Active'.

Why the DEFAULT Constraint Matters

The DEFAULT constraint guarantees consistency and prevents empty values in crucial fields. By setting meaningful defaults, you simplify data entry and maintain reliable, high-quality databases.

SQL Constraints: A Complete Guide (With Examples)

Learn what constraints in SQL are, why they matter, and how to apply each one to build robust, error-free databases. Includes ready-to-use code samples for PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT.

Primary Key Constraint

A PRIMARY KEY uniquely identifies each row and disallows NULL. It’s the backbone of relational design and speeds up lookups and joins.

  • Uniqueness: No two rows share the same key value.
  • NOT NULL: The key column(s) cannot be NULL.
  • Composite Keys: May span multiple columns when a single column isn’t enough.
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name       VARCHAR(100),
    Email      VARCHAR(100)
);

Foreign Key Constraint (Referential Integrity)

A FOREIGN KEY enforces relationships by referencing a parent table’s primary key, preventing orphaned records.

  • Integrity: Values must exist in the parent table.
  • Cascades: Use ON DELETE/UPDATE CASCADE to auto-sync changes.
  • Multiple FKs: A table can reference multiple parents.
CREATE TABLE Orders (
    OrderID    INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Unique Constraint

The UNIQUE constraint ensures column values are distinct. Perfect for emails, usernames, product codes.

  • No Duplicates: Enforced at the database layer.
  • Multiple UNIQUEs: A table can have several UNIQUE columns.
  • NULLs: Typically allows a single NULL per column (varies by DB engine).
CREATE TABLE Users (
    UserID   INT PRIMARY KEY,
    Username VARCHAR(100) UNIQUE,
    Email    VARCHAR(100) UNIQUE
);

NOT NULL Constraint

NOT NULL makes a column mandatory—no missing values. Great for essential fields like names or statuses.

  • Completeness: Prevents empty required fields.
  • Quality: Improves reliability of downstream analytics.
CREATE TABLE Orders (
    OrderID      INT PRIMARY KEY,
    ProductName  VARCHAR(255) NOT NULL,
    OrderStatus  VARCHAR(100) NOT NULL
);

CHECK Constraint

CHECK validates values against a condition—ideal for enforcing business rules and valid ranges.

  • Validation: Ensures only acceptable values are stored.
  • Flexible Logic: Supports comparisons and AND/OR conditions.
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name       VARCHAR(100),
    Age        INT CHECK (Age > 18),
    Position   VARCHAR(100)
);

DEFAULT Constraint

DEFAULT assigns a value automatically when none is provided, keeping data consistent.

  • Fewer Errors: Prevents unintended blanks.
  • Common Uses: Status, timestamps, flags, counters.
CREATE TABLE Users (
    UserID        INT PRIMARY KEY,
    UserName      VARCHAR(100),
    AccountStatus VARCHAR(50) DEFAULT 'Active'
);

Best Practices

  • Name constraints explicitly (e.g., CONSTRAINT uq_users_email) for clarity.
  • Validate at the database layer (NOT NULL, CHECK) rather than only in app code.
  • Index wisely: UNIQUE creates an index; avoid overlapping redundant indexes.
  • Use cascades carefully to prevent accidental mass deletes/updates.
  • Document business rules that your CHECK/DEFAULT constraints encode.

Common Errors & How to Fix

Violation of PRIMARY/UNIQUE: Remove duplicates or change the key value.
FOREIGN KEY constraint fails: Insert parent row first, or use valid IDs; consider cascades if appropriate.
CHECK violated: Ensure values meet the rule (e.g., positive price, minimum age).
Cannot insert NULL: Provide a value or adjust column to allow NULL / add DEFAULT if logical.

FAQ: SQL Constraints

Q1: What is a constraint in SQL?
A rule enforced by the database to maintain data integrity (e.g., PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT).

Q2: Difference between PRIMARY KEY and UNIQUE?
Both enforce uniqueness, but PRIMARY KEY also implies NOT NULL and identifies rows. A table has one PRIMARY KEY but can have many UNIQUE constraints.

Q3: Can a column with UNIQUE be NULL?
Usually yes—often one NULL per column (implementation-specific).

Q4: When should I use CHECK?
When business rules (ranges, thresholds, logical conditions) must be guaranteed by the database.

Q5: What are the types of constraints in SQL?
The main types of SQL constraints are PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT. These ensure valid, consistent, and reliable data.

Q6: Can you give an example of SQL constraints?
Example:
CREATE TABLE Users ( UserID INT PRIMARY KEY, Email VARCHAR(100) UNIQUE, Age INT CHECK(Age > 18) );
This combines PRIMARY KEY, UNIQUE, and CHECK constraints in one table.

Q7: What are database constraints?
Database constraints are rules that enforce data accuracy and consistency. They prevent duplicates, invalid values, and maintain valid relationships between tables.

Q8: Why are constraints important in a database?
They protect data integrity, stop duplicate/missing values, enforce valid links between tables, and improve query performance.

Q9: What is the difference between data constraints and integrity constraints?
Data constraints (NOT NULL, UNIQUE, CHECK) apply at the column level. Integrity constraints (PRIMARY KEY, FOREIGN KEY) maintain consistency across tables.

Q10: What are CHECK and DEFAULT constraints in SQL?
CHECK enforces conditions (e.g., salary > 0). DEFAULT inserts a predefined value when none is provided (e.g., status = ‘Active’).

SQL Constraints — Quick Reference

Constraint Ensures Typical Use Example (DDL)
PRIMARY KEY Unique row identity; no NULL IDs like CustomerID, OrderID 
CREATE TABLE T(
  id INT PRIMARY KEY
);
FOREIGN KEY Referential integrity to parent table Links Orders→Customers 
FOREIGN KEY (customer_id)
  REFERENCES Customers(id)
UNIQUE No duplicate values Email, Username, SKU 
email VARCHAR(255) UNIQUE
NOT NULL Value required (no NULL) Names, statuses, required fields 
name VARCHAR(100) NOT NULL
CHECK Values meet a condition Age > 18, Price > 0 
age INT CHECK (age > 18)
DEFAULT Auto-fill when not provided Status, timestamps, flags 
status VARCHAR(20) DEFAULT 'Active'

🧠 Test Your Knowledge: SQL Constraints Quiz

Answer these 10 questions to check how well you understand SQL constraints. Your score will be shown at the end!

🎥 Learn SQL Constraints in Action

Watch this detailed video to understand SQL Constraints with real-world examples. It will help you connect the theory with practical usage in databases.

🚀 Call to Action: Take Your SQL Skills to the Next Level

✨ Explore Additional SQL Tutorials and Real-World Practice

Now that you’ve mastered SQL Constraints, it’s time to expand your knowledge and practice with more advanced SQL topics. The best way to improve is through hands-on experience. Explore the tutorials below and practice on real-world datasets to level up your SQL expertise:

These resources will help you understand complex SQL concepts with real-world examples. Keep practicing and refining your skills — hands-on experience is the fastest way to become an SQL expert.

Vista Academy – 316/336, Park Rd, Laxman Chowk, Dehradun – 248001
📞 +91 94117 78145 | 📧 thevistaacademy@gmail.com | 💬 WhatsApp
💬 Chat on WhatsApp: Ask About Our Courses