🔑 SQL PRIMARY KEY – Uniquely Identify Each Row

🧲 Introduction – What is SQL PRIMARY KEY?

The PRIMARY KEY constraint in SQL ensures that each row in a table can be uniquely identified. It combines UNIQUE and NOT NULL, making it a fundamental element of relational database design.

🎯 In this guide, you’ll learn:

• How to declare a PRIMARY KEY on one or more columns
• Differences from UNIQUE constraints
• Indexing, enforcement, and real-world examples

---

✅ 1. Basic PRIMARY KEY Syntax

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

✅ Ensures each id is unique and never NULL.

---

🧱 2. Composite PRIMARY KEY

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id)
);

✅ Enforces uniqueness across multiple columns.

---

🧩 3. Add PRIMARY KEY with ALTER TABLE

ALTER TABLE customers
ADD CONSTRAINT pk_customer_id PRIMARY KEY (id);

✅ Adds a named primary key constraint after creation.

---

⚙️ 4. Behavior & Rules

• Only one PRIMARY KEY per table
• Cannot contain NULL values
• Automatically creates a unique index
• Composite keys combine multiple fields for uniqueness

---

🆚 5. PRIMARY KEY vs UNIQUE

Feature	PRIMARY KEY	UNIQUE Constraint
Allows NULLs	❌ No	✅ Yes (1+ allowed)
Max per table	1	Many allowed
Implies index	✅ Yes	✅ Yes

---

📘 Best Practices

✅ Recommended	❌ Avoid This
Always define a primary key for each table	Using duplicate or NULL primary values
Use meaningful, minimal fields (e.g., ID)	Using large text or composite keys unnecessarily
Name constraints clearly	Letting DBMS assign default names

---

📌 Summary – Recap & Next Steps

The PRIMARY KEY is the cornerstone of relational integrity. It guarantees row uniqueness, enabling joins, lookups, and indexing.

🔍 Key Takeaways:

• Ensures every row has a unique, non-null identifier
• Automatically indexed
• One per table—can be single or multi-column

⚙️ Real-World Relevance:
Used in user IDs, order numbers, reference IDs, and lookup joins across all database models.

➡️ Next: Learn FOREIGN KEY to relate tables, or explore UNIQUE and NOT NULL combinations.

---

❓ FAQ – SQL PRIMARY KEY

❓ Can a table have two PRIMARY KEYs?

❌ No. Only one per table, but it can span multiple columns.

❓ Does PRIMARY KEY imply UNIQUE?

✅ Yes. It’s a combination of UNIQUE + NOT NULL.

❓ Can I change a PRIMARY KEY?

✅ Yes. You must drop it first, then add a new one.

❓ Is PRIMARY KEY always indexed?

✅ Yes. Indexing is automatic for primary keys.

---

Share Now :