Surrogate Keys
"It is a unique key whose only significance is to act as the primary identifier of an object or entity and is not derived from any other data in the database"
Imagine a m-to-n relationship in a relational database. We may have users and roles, where a user can have multiple roles and a role can be assigned to multiple users.
We can model this relationship with a join table containing the primary keys of the users and roles tables.
| user_id | role_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 2 |
A surrogate key is not derived from any data in the database, we could use a UUID or an auto-incrementing integer.
| id | user_id | role_id |
|---|---|---|
| 1eba… | 1 | 1 |
| d784… | 1 | 2 |
| 7673… | 2 | 2 |