Introduction
Primary keys and foreign keys are fundamental concepts in relational database design. They ensure data integrity, maintain relationships between tables, and allow efficient querying.
Why They Matter
- Primary Keys: Guarantee uniqueness of each record.
- Foreign Keys: Maintain referential integrity between tables.
Real-world analogy:
Imagine a national ID system. Your unique ID number is like a primary key. When applying for a loan, the bank references that ID (foreign key) to verify your identity and link records.
Core Concepts
Primary Key
- Uniquely identifies each row in a table.
- Cannot contain NULL values.
- Typically auto-incremented (e.g., SERIAL in PostgreSQL).
SQL Example: Creating a Primary Key
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100) UNIQUE
);
Foreign Key
- Establishes a relationship between two tables.
- References a primary key in another table.
- Enforces referential integrity.
SQL Example: Creating a Foreign Key
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
product VARCHAR(50)
);
Real-World Use Cases
- E-commerce: Link orders to customers.
- Banking: Associate transactions with accounts.
- Healthcare: Connect patients with medical records.
Common Mistakes and Anti-Patterns
- No primary key: Leads to duplicate records and difficulty in referencing.
- Foreign key mismatch: Using incompatible data types.
- Disabling constraints for performance: Risks data inconsistency.
Performance and Scalability Implications
- Primary keys improve indexing and query speed.
- Foreign keys add overhead during inserts/updates but ensure data consistency.
- For high-write systems, consider optimized indexing strategies.
RDBMS Comparison
| Feature | PostgreSQL | MySQL | Oracle |
|---|---|---|---|
| Auto Increment PK | SERIAL | AUTO_INCREMENT | SEQUENCE |
| FK Behavior | Full enforcement | Can be deferred | Full enforcement |
| Cascade Options | Supported | Supported | Supported |
When to Use vs When to Avoid
Use Primary Keys When:
- Every table should have one for uniqueness.
- Needed for relationships and indexing.
Avoid Complex Composite Keys When:
- Surrogate keys simplify relationships and indexing.
Use Foreign Keys When:
- You need referential integrity between tables.
Avoid When:
- In some high-scale systems where constraints are managed at the application level.
Best Practices & Optimization Tips
- Always define a primary key for each table.
- Use integer-based surrogate keys for performance.
- Match data types exactly for foreign keys.
- Use ON DELETE CASCADE cautiously to avoid accidental data loss.
- Index foreign key columns for faster joins.
SQL Example: Joining Tables
SELECT c.name, o.product
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;
Output:
| name | product |
|---|---|
| John Smith | Laptop |
| Jane Doe | Keyboard |
Conclusion & Key Takeaways
Primary and foreign keys are critical for relational database integrity. They define relationships, enforce consistency, and optimize query performance.
Key Points:
- Primary keys uniquely identify rows.
- Foreign keys maintain relationships between tables.
- Proper use ensures data integrity and efficient queries.
FAQ
1. What is a primary key?
A unique identifier for each row in a table.
2. Can a table have multiple primary keys?
No, but it can have a composite primary key with multiple columns.
3. What is a foreign key?
A reference to a primary key in another table.
4. Can a foreign key reference a non-primary key?
Yes, if the referenced column has a unique constraint.
5. What happens if I delete a record referenced by a foreign key?
You’ll get an error unless you use cascading rules (e.g., ON DELETE CASCADE).
6. Are primary keys always integers?
No, but integers are efficient and commonly used.
7. Can I have a table without a primary key?
Technically yes, but it’s bad practice.
8. Do foreign keys impact performance?
They add overhead on writes but improve data integrity.
9. Can foreign keys be NULL?
Yes, if the relationship is optional.
10. Which is better: natural key or surrogate key?
Surrogate keys are preferred for simplicity and performance.