🔑 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 KEYon one or more columns - Differences from
UNIQUEconstraints - 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 KEYper 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 :
