4 ms·

I don't understand why. I see constraints doing the same job as objects to in programming: they keep the data and the rules for manipulating that data together,

I don't understand why. I see constraints doing the same job as objects to in programming: they keep the data and the rules for manipulating that data together, ensuring that the invariants are always respected. If "you can't have a price <= 0" is a business rule then it should be enforced in all layers: in the UI because you want to warn the user they're trying to do something incorrect without sending obviously invalid data to the server, in the back-end because you don't want to write incorrect data to the database, and in the database because you don't know that someone else will not write "just a quick app" that will bypass your back-end and mess up the data.

vbezhenar 9y ago
Problem with constraints is that they reflect current business logic, but database stores past data. This is inherent mismatch and it'll cause conflicts. You can't add constraint which conflict with old data, yet you need to check that constraint for current data, therefore you must implement those constraints somewhere else and if you have those constraints there anyway, why bother with additional ones. If you have duplicate constraints, you must not forget to keep them in sync. I'm not saying that constraints are bad, database without constraints would be a very strange beast, even UTF-8 text is a constraint upon arbitrary byte strings, but it's a dangerous construct and must not be misused, otherwise it'll cause more harm than good.
- always_good 9y ago
  > You can't add constraint which conflict with old data Of course you can. For example, constraints inside triggers. A simple insert trigger will only apply on records going forward. Also, the reason why you bother with "additional" constraints by implementing them on the database is because the database is typically the thing that can enforce them. Unless you go out of your way to roll your own single-write via queue or something. I'm not sure I agree with your take-away since you can just drop constraints. Easy come, easy go. Meanwhile bad data is easy come and potentially a nightmare to fix.
  - Demiurge 9y ago
    >Of course you can. For example, constraints inside triggers. A simple insert trigger will only apply on records going forward. Or you can just expand the constraint to superset of two, or figure out how the old data needs to be treated by new system and migrate it. There are many right options if you actually care about data integrity that are not as easy as not caring. Doesn't make it good general advice not to care.
- Demiurge 9y ago
  Saying a feature of database that validates the data inside a database is dangerous is very much like saying seatbelts are dangerous because you can drawn. Anything can be misused, but there is a clear use case, because an ACID database provides a whole transactional, reliable and coherent application that can enforce rules no other layer in the system can, unless it reimplements the entire DB with sql validation and type checking.
- Slansitartop 9y ago
  > You can't add constraint which conflict with old data, yet you need to check that constraint for current data, If there was a way to distinguish "old" and "new" data, you could write your constraint to honor the difference. > therefore you must implement those constraints somewhere else and if you have those constraints there anyway, why bother with additional ones. IMHO, if you must only have one set of constraints, put it in the data store. The thing about constraints is that they provide pretty solid guarantees that let you better reason about the data at its base level. You can't reason well about data that was "checked" by dozens of different versions of validations over time. What you have in that case is garbage in need of cleanup.

vbezhenar 9y ago

Problem with constraints is that they reflect current business logic, but database stores past data. This is inherent mismatch and it'll cause conflicts. You can't add constraint which conflict with old data, yet you need to check that constraint for current data, therefore you must implement those constraints somewhere else and if you have those constraints there anyway, why bother with additional ones. If you have duplicate constraints, you must not forget to keep them in sync. I'm not saying that constraints are bad, database without constraints would be a very strange beast, even UTF-8 text is a constraint upon arbitrary byte strings, but it's a dangerous construct and must not be misused, otherwise it'll cause more harm than good.

always_good 9y ago
> You can't add constraint which conflict with old data Of course you can. For example, constraints inside triggers. A simple insert trigger will only apply on records going forward. Also, the reason why you bother with "additional" constraints by implementing them on the database is because the database is typically the thing that can enforce them. Unless you go out of your way to roll your own single-write via queue or something. I'm not sure I agree with your take-away since you can just drop constraints. Easy come, easy go. Meanwhile bad data is easy come and potentially a nightmare to fix.
- Demiurge 9y ago
  >Of course you can. For example, constraints inside triggers. A simple insert trigger will only apply on records going forward. Or you can just expand the constraint to superset of two, or figure out how the old data needs to be treated by new system and migrate it. There are many right options if you actually care about data integrity that are not as easy as not caring. Doesn't make it good general advice not to care.
Demiurge 9y ago
Saying a feature of database that validates the data inside a database is dangerous is very much like saying seatbelts are dangerous because you can drawn. Anything can be misused, but there is a clear use case, because an ACID database provides a whole transactional, reliable and coherent application that can enforce rules no other layer in the system can, unless it reimplements the entire DB with sql validation and type checking.
Slansitartop 9y ago
> You can't add constraint which conflict with old data, yet you need to check that constraint for current data, If there was a way to distinguish "old" and "new" data, you could write your constraint to honor the difference. > therefore you must implement those constraints somewhere else and if you have those constraints there anyway, why bother with additional ones. IMHO, if you must only have one set of constraints, put it in the data store. The thing about constraints is that they provide pretty solid guarantees that let you better reason about the data at its base level. You can't reason well about data that was "checked" by dozens of different versions of validations over time. What you have in that case is garbage in need of cleanup.