SQL Constraints and Indexes – NOT NULL, UNIQUE, PRIMARY KEY, INDEX Explained

Introduction – Why Learn SQL Constraints & Indexes?

When building robust database systems, it’s not enough to just store data—you must also ensure its accuracy, consistency, and performance. SQL constraints enforce rules on the data, while indexes help accelerate queries.

Learning SQL constraints and indexes helps you:

• Prevent invalid or duplicate entries
• Maintain relationships between tables
• Improve query execution times dramatically

In this guide, you’ll learn how to:

• Apply constraints like NOT NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, and DEFAULT
• Use INDEX and AUTO INCREMENT to optimize performance and automate key generation

Topics Covered

SQL NOT NULL – Enforce Required Fields

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

Ensures that the name column cannot be left empty.
Common for essential fields like email, username, or password.

SQL UNIQUE – Enforce Distinct Values

CREATE TABLE users (
  email VARCHAR(255) UNIQUE
);

Prevents duplicate values in a column.
Perfect for fields like email or social security numbers.

SQL PRIMARY KEY – Identify Rows Uniquely

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

Uniquely identifies each row and implies NOT NULL + UNIQUE.
Only one primary key allowed per table.

SQL FOREIGN KEY – Maintain Relationships

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES users(id)
);

Links rows in one table to another, enforcing referential integrity.
Prevents insertion of invalid references.

SQL CHECK – Apply Value Constraints

CREATE TABLE products (
  price DECIMAL(10,2),
  CHECK (price > 0)
);

Ensures a logical condition is met for values.
Use to validate age, price, quantity, etc.

SQL DEFAULT – Assign Values Automatically

CREATE TABLE tasks (
  status VARCHAR(50) DEFAULT 'pending'
);

Assigns a default value when no input is provided.
Helpful for status, created_at, or boolean flags.

SQL INDEX – Boost Query Speed

CREATE INDEX idx_lastname
ON employees (last_name);

Improves SELECT and JOIN performance.
Indexes speed up search but may slow INSERT/UPDATE operations slightly.

SQL AUTO INCREMENT – Generate Unique IDs

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

Automatically assigns a unique number to new rows.
Used for primary key columns like user ID, invoice number, etc.

Best Practices for Constraints & Indexes

Summary – Recap & Next Steps

SQL constraints ensure data quality and integrity, while indexes provide the speed needed for large datasets and fast access.

Key Takeaways:

• Constraints (NOT NULL, UNIQUE, CHECK, PRIMARY, FOREIGN) validate data rules
• Indexes and AUTO INCREMENT optimize performance and automation
• Proper use of constraints prevents errors and simplifies logic

Real-World Relevance:
Used in enterprise databases, APIs, CRMs, banking systems, and reporting tools where data correctness and speed are non-negotiable.

Next Up: Dive into SQL Indexing Strategies & Query Optimization Techniques.

FAQs – SQL Constraints & Indexes

Can a column be both UNIQUE and NOT NULL?
Yes! This combination ensures every row has a value, and each value is distinct.

What’s the difference between PRIMARY KEY and UNIQUE?
PRIMARY KEY implies both NOT NULL and UNIQUE, while UNIQUE allows NULL unless stated otherwise.

How many PRIMARY KEYs can a table have?
Only one PRIMARY KEY per table, though it can include multiple columns (composite key).

Can I add constraints after table creation?
Yes, using ALTER TABLE:

ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 18);

Does every column need an index?
No. Index only those used in frequent queries, JOIN, or WHERE clauses.