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.
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.
Comments
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.
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.