[ewang1]

There are benefits for marking a row as deleted w/ a boolean column though. Undo-ing deletes would be easy to implement, and retaining references to deleted records would be simple as well.

The solution the problem you described would be to use a surrogate key column (typically a UUID/auto-increment), and not natural keys.

[jsprogrammer]

There are reasons for everything in the database. Undo and historical records were the primary use case.

All tables had a unique, integer primary key.

However, if you want to enforce a uniqueness constraint across your data [eg. UNIQUE(name, location)], the constraint breaks when you introduce the boolean deleted column [and UNIQUE(name, location, deleted) does not provide the appropriate semantics]. The application semantics must be provided at some other level than SQL column constraints.