Using Physical Primary Keys as Foreign Keys and Implementing Optimistic Locks

Using Physical Primary Keys as Foreign Keys

Relational databases consist of multiple tables that can be linked via foreign keys. Each table should have a physical primary key—typically an auto‑increment integer or a UUID—so that foreign‑key relationships always reference a stable, unique identifier.

Logical keys (non‑id unique columns) may become non‑unique as business rules evolve, breaking foreign‑key integrity. Therefore, physical primary keys are preferred for both primary key and foreign‑key purposes.

Applying Optimistic Locking for Dependent Record Updates

In scenarios such as task‑hand‑overs where only the first transaction should succeed, a locking strategy is required. Databases offer pessimistic locks (e.g., SELECT * FROM t FOR UPDATE) and optimistic locks, which are implemented at the application level.

Optimistic locking adds a version column to a table. The column is incremented on each update, and the update statement checks that the stored version matches the expected value. If the versions differ, the update fails and the application must reload the record.

UPDATE studentVersion SET ver = ?, name = ? WHERE id = ? AND ver = ?

Updating Independent State Records Without Locks

When updates are independent of previous state—such as starting or stopping a virtual machine—no locking is necessary. The version check can be omitted, and a simple update suffices.

UPDATE studentVersion SET name = ? WHERE id = ?

Summary

When designing relational tables, include an auto‑increment or UUID id column as the physical primary key and a numeric version column for optimistic locking. Use the physical primary key for foreign‑key relationships, apply optimistic locks to improve concurrency performance, and skip locking when record states are independent.