Surrogate Key

Definition

A surrogate key is an artificial identifier — most commonly an auto-incrementing integer or a generated UUID — with no business meaning whatsoever, created purely so the database has a simple, stable way to uniquely identify each row.

How It Works

sql
CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,   -- surrogate key: means nothing outside this table
  email VARCHAR(150) UNIQUE NOT NULL,  -- natural candidate key, kept as an alternate key
  name VARCHAR(150) NOT NULL
);

id here carries no meaning a human would recognize — customer id = 4821 tells you nothing about who that customer is. That's precisely the point: because it has no business meaning, it never NEEDS to change for business reasons (unlike an email, which might). It becomes the table's stable primary key, while a genuinely meaningful natural key (like email) can still be kept and enforced as a UNIQUE alternate key.

Edge Cases and Pitfalls

• A surrogate key does not remove the need to ALSO enforce uniqueness on the real natural key — skipping that (relying only on the surrogate id) can let two rows for "the same real-world entity" (e.g. the same email registered twice) slip in as if they were different customers.
• Auto-incrementing integer surrogate keys can leak information (an increasing id reveals roughly how many rows/orders/customers exist and in what order they were created) — some systems deliberately use randomly-generated UUIDs instead specifically to avoid this.
• Surrogate keys generated independently on multiple systems (e.g. offline mobile clients each generating their own new customer id) can collide unless a globally-unique generation scheme (like a proper UUID, not a simple counter) is used.

Key Takeaways

• Surrogate key = an artificial, meaningless identifier chosen purely for database convenience and stability.
• It should typically be paired with a UNIQUE constraint on any real natural key, not used as a replacement for one.
• Trade-off vs. natural key (5.7): stability and simplicity, at the cost of having no meaning outside the database.