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