Primary and Foreign Keys | Database Architecture Constraints

# CHAPTER 7

Primary Keys and Foreign Keys #

1. Introduction #

2. Learning Objectives #

• Define the mathematical strictness of a Primary Key.

• Differentiate between Natural Keys and Surrogate Keys.

• Architect Composite Primary Keys.

• Define a Foreign Key constraint.

• Understand and enforce Referential Integrity.

3. The Primary Key (The Anchor) #

The Absolute Rules of a Primary Key:

1. 1. It must be UNIQUE. No two rows can ever share the same key.

1. 2. It must be NOT NULL. A row cannot exist without a key.

1. 3. It must never change.

4. Natural Keys vs. Surrogate Keys #

• Natural Key: A unique attribute that already exists in the real world (e.g., a Social Security Number, an Email Address, or a Car VIN number).

• Surrogate Key: A meaningless, computer-generated integer (e.g., id = 1, 2, 3... generated via AUTOINCREMENT).

The Industry Standard: Always use Surrogate Keys (AUTOINCREMENT). Why? Because real-world data changes. If you use an Email as a Primary Key, and the user changes their email, you have to update the Primary Key, which shatters all relationships across the database. Meaningless integers never change.

5. Composite Primary Keys #

If User #5 places an order, the orders table has a column named userid. We place the number 5 in that column. That column is the Foreign Key.

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    total_price DECIMAL(10,2),
    user_id INT,
    -- Architecting the physical bridge!
    FOREIGN KEY (user_id) REFERENCES users(id)
);

7. Referential Integrity (The Iron Wall) #

Referential Integrity is a strict security system inside the database engine. It guarantees that a Foreign Key value MUST point to a physically existing Primary Key.

• If a hacker tries to insert an order for user_id = 99, but User 99 does not exist in the users table, the database violently rejects the INSERT and throws a fatal error.

• It prevents "Orphaned Records"—data that belongs to nothing, which corrupts accounting systems.

8. Mini Project: Cascading Actions #

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

*(Now, if User 5 is deleted, the database automatically hunts down and destroys all 10 of their orders instantly, keeping the database perfectly clean!).*

9. Common Mistakes #

• Data Type Mismatches: The single most common error when architecting relationships. If users.id is an INT, but your orders.userid column is defined as a VARCHAR or a BIGINT, the database engine will refuse to create the Foreign Key. The data types must be mathematically identical.

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the philosophical and architectural differences between Natural Keys and Surrogate Keys. Why has the industry overwhelmingly standardized on Surrogate Keys for Relational Database schema design?

• Q: A junior developer attempts to execute a DELETE statement to remove a category from the database, but the DBMS throws a "Foreign Key Constraint Fails" error. Explain the concept of Referential Integrity causing this block, and how implementing ON DELETE CASCADE alters this behavior.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #