🧷 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

🔖 Topic	📄 Description
🚫 SQL NOT NULL	Ensures a column cannot have NULL values
🔐 SQL UNIQUE	Ensures all values in a column are distinct
🔑 SQL PRIMARY KEY	Uniquely identifies each row in a table
🔗 SQL FOREIGN KEY	Establishes a link between two tables
✅ SQL CHECK	Restricts values in a column using custom logic
🧩 SQL DEFAULT	Assigns a default value if no value is provided
📌 SQL INDEX	Improves performance of SELECT queries
🔢 SQL AUTO INCREMENT	Automatically generates numeric values (e.g., IDs)

---

🚫 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

✅ Do This	❌ Avoid This
Use NOT NULL on required fields	Don’t allow NULLs unless necessary
Apply UNIQUE to identity columns	Avoid duplicate-sensitive fields without it
Define FOREIGN KEYS for data relationships	Don’t hard-code foreign relations manually
Use CHECK for business rules	Avoid constraints in app code only
Index frequently queried columns	Don’t over-index — it affects write performance
Use AUTO INCREMENT for identity	Avoid manual ID generation

---

📌 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.

---

Share Now :