Depends on the database, sometimes the database config, as to whether they'll actually be enforced or not, or in what situations data might evade enforcement of the constraints…
Applies to vendors, too. Had some data in Rackspace Files where "list files" would say X existed, but "GET X" got you a 404. Had an AWS RDS instance; query on it returned no results. Adding the "don't use the index" index hint caused it to return data. (Allegedly, this bug was fixed, but we had migrated off by that point, so I never got to confirm it.)
Conversely, I do like DB constraints, because if the DB constraint doesn't exist, then I guarantee you the production DB has a row that is a counter-example to whatever constraint you think the data should obey…
> Had some data in Rackspace Files where "list files" would say X existed, but "GET X" got you a 404.
Well yes, Rackspace Files (aka OpenStack Swift) is eventually consistent. It says so literally in the first sentence of the documentation [1]. But this discussion is about relational databases with ACID guarantees, where the C is literally "consistent".
Applies to vendors, too. Had some data in Rackspace Files where "list files" would say X existed, but "GET X" got you a 404. Had an AWS RDS instance; query on it returned no results. Adding the "don't use the index" index hint caused it to return data. (Allegedly, this bug was fixed, but we had migrated off by that point, so I never got to confirm it.)
Conversely, I do like DB constraints, because if the DB constraint doesn't exist, then I guarantee you the production DB has a row that is a counter-example to whatever constraint you think the data should obey…