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 :
