Regardless of the constraint on the key, the design fact remains that deleting a user record that might, for example, have associated transaction data (like subscription payments) is a little complex.

You don't want to cascade that deletion to a record of credit card charges, but you also need to make sure that all queries respect that the user record might now be deleted - ie make it an outer-join.

It's far more robust to add an active/inactive field.

The longer an organization has been around, the more interconnected the database is, and the more consequent changes are needed to accommodate a core database change.

I would be surprised if many many organizations have some hack like this under the covers.