What is the difference between a primary key and a foreign key? Why are they important?

Question

Accepted Answer

## Primary Key

A primary key is a column (or combination of columns) that **uniquely identifies each row** in a table.

```sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,  -- Primary key
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100)
);
```

**Characteristics:**
- Must be unique for every row
- Cannot be NULL
- Only ONE primary key per table
- Often auto-incremented (SERIAL, AUTO_INCREMENT)

## Foreign Key

A foreign key is a column that **references the primary key of another table**, creating a relationship.

```sql
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),  -- Foreign key
  total DECIMAL(10, 2),
  created_at TIMESTAMP
);
```

**Characteristics:**
- Can contain duplicate values
- Can be NULL (optional relationship)
- Multiple foreign keys allowed per table
- Creates parent-child relationship

## Visual Example

```
users (parent)              orders (child)
+----+-------+              +----+---------+-------+
| id | name  |              | id | user_id | total |
+----+-------+              +----+---------+-------+
| 1  | Alice | <────────────| 1  | 1       | 99.99 |
| 2  | Bob   | <────────────| 2  | 1       | 49.99 |
+----+-------+         └────| 3  | 2       | 25.00 |
     PK                     +----+---------+-------+
                                   FK
```

## Why They're Important

| Key Type | Purpose |
|----------|---------|
| **Primary Key** | Unique identification, fast lookups |
| **Foreign Key** | Referential integrity, relationships |

**Referential Integrity Example:**
```sql
-- This will FAIL if user 999 doesn't exist
INSERT INTO orders (user_id, total) VALUES (999, 50.00);
-- Error: foreign key constraint violation

-- This will also FAIL
DELETE FROM users WHERE id = 1;
-- Error: orders still reference this user
```

## Composite Keys

```sql
-- Primary key with multiple columns
CREATE TABLE order_items (
  order_id INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER,
  PRIMARY KEY (order_id, product_id)  -- Composite PK
);
```