Surrogate vs Natural Keys

Surrogate keys (auto-increment ID) are system-generated and stable. Natural keys (email, SSN) have real-world meaning but can change and may be long.

Example

-- Natural key: email as PK
CREATE TABLE users (email VARCHAR(255) PRIMARY KEY, name VARCHAR(100));
-- Problem: if email changes, all FK references break

-- Surrogate key: stable ID
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  name VARCHAR(100)
);

SQL

Full Editor

Pro Tip

Use surrogate keys as PK, but keep natural keys as UNIQUE constraints.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned