🐕 Introduction: Dogs and Owners
Imagine a neighborhood with lots of dogs and owners.
- The Dog Collar (Primary Key): Every dog has a collar with a unique ID code engraved on it (like "DOG-101"). No two dogs can ever have the same collar code. This is the Primary Key. It uniquely identifies each dog.
- The Owner ID Badge: Each owner has a unique badge (like "OWNER-50").
- The Leash (Foreign Key): To know which dog belongs to which owner, we attach a tag to the dog's collar that says: "Belongs to Owner ID: OWNER-50".
- This owner code on the dog's collar points directly to the Owners table.
- In databases, this pointer is called a Foreign Key.
With these keys, we can perfectly link dogs to owners without ever mixing them up!
🔑 Key Concepts and Visual Mapping
OWNERS TABLE (Parent) DOGS TABLE (Child)
+--------------------------+ +--------------------------+
| owner_id (PK) [1] <-----+-------------+ | dog_id (PK) |
| owner_name | | dog_name |
+--------------------------+ | owner_id (FK) [1] |
+--------------------------+
Primary Key (PK) vs. Foreign Key (FK)
| Rule | Primary Key (PK) | Foreign Key (FK) |
|---|---|---|
| Purpose | Uniquely identifies a row in its own table. | Links a row to another table (usually a PK). |
| Uniqueness | Must be 100% unique. No duplicates allowed. | Can contain duplicate values (multiple dogs can belong to the same owner). |
| Empty Values | Cannot be empty (NULL is forbidden). | Can be empty (NULL allowed if a dog has no owner). |
| Count | Only one Primary Key per table. | A table can have multiple Foreign Keys pointing to different tables. |
Types of Primary Keys:
- Surrogate Key: A system-generated number (like an auto-incrementing ID: 1, 2, 3...) that has no real-world meaning but is guaranteed to be unique.
- Natural Key: A real-world unique attribute (like a Social Security Number or vehicle VIN).
- Composite Key: A primary key made of two or more columns combined (e.g.
student_id+course_idin a registration table).
💻 Code Examples
Let's see how we write SQL to define these keys and run query matches.
SQL Setup
-- Owners Table
CREATE TABLE owners (
owner_id INTEGER PRIMARY KEY, -- Primary Key
owner_name TEXT NOT NULL
);
-- Dogs Table
CREATE TABLE dogs (
dog_id INTEGER PRIMARY KEY, -- Primary Key
dog_name TEXT NOT NULL,
owner_id INTEGER, -- Foreign Key column
FOREIGN KEY (owner_id) REFERENCES owners(owner_id)
);Multi-Language Execution
⚠️ Common Mistakes
1. Orphaned Child Rows
Deleting an owner (parent) but leaving their dogs (children) behind with an ID pointing to nothing.
- Bad: Default deletes that leaves dangling pointers.
- Good: Use
ON DELETE CASCADEto automatically delete the dogs when their owner is removed:sqlFOREIGN KEY (owner_id) REFERENCES owners(id) ON DELETE CASCADE
2. Using Changing Attributes as Primary Keys
Using username or email as a Primary Key. If the user changes their email, you have to update the foreign keys in all other tables, which is slow and prone to errors. Use a stable system ID instead.
🔍 Interview Corner
Q1: What is a Composite Primary Key, and when should you use it?
A Composite Primary Key is a primary key that consists of two or more columns. It is used when no single column can guarantee uniqueness, but their combination does. A classic example is a course_registrations table where the primary key is (student_id, course_id).
Q2: What is the difference between a Surrogate Key and a Natural Key?
- A Natural Key is a unique attribute that already exists in the real world (like a barcode or Passport Number).
- A Surrogate Key is a synthetic, artificial value created by the database engine (like an auto-incrementing integer or UUID) solely to act as the primary key.
📝 Summary
- A Primary Key is a unique barcode tag for a single row.
- A Foreign Key is a leash that connects a row to a primary key in another table.
- Enforcing foreign keys prevents broken links inside your database.