In what situations is it advisable to use a synthetic key in a database?

A synthetic key is an artificially created unique identifier for a record in a database that does not carry business meaning (for example, an auto-incrementing numeric ID or UUID).

It is advisable to use a synthetic key in the following situations:

• Absence of a natural unique identifier: when there is no suitable unique field or combination of fields in the data.

• Complex composite keys: if a natural key consists of multiple fields, which complicates indexing and relationships, a synthetic key simplifies the structure.

• Changeability of natural keys: if natural keys can change, it leads to data integrity issues; a synthetic key is usually immutable.

• Unification and standardization: using synthetic keys simplifies development and maintenance, especially when integrating with other systems.

Example: a user table where the natural key is email, but it can change. Instead of using email as the primary key, a user_id field with auto-increment is created and used as a synthetic key.