I've been wondering lately. Lets say we're doing a webapp with JSF+Spring+JPA/Hibernate (or well any other technologies) and lets say we have a "User" entity. We want the User to have a unique login. If we want to do it then we can put a @UniqueConstraint on the "Login" column, but still our application has to check during user registration whether the user input is valid (unique) or not, without that and only with the DB constraint we will simply get an error. This made me think, are DB constraints really necessary/helpful? The only two times I can think off when they would give us any benefits would be when someone tries to hack us (but I guess our app should be SQL injection proof anyway) or we try to change the DB content manually (which shouldn't really happen). Actually now that I think about it, are DB constraints in general necessary/good practice? Like the lenght of a string etc.
For me, categorically yes, see Database as a Fotress by Dan Chak from 97 Thinks Every Software Architect Should Know. He says it much better than I could.
Yes, they are. They enforce data integrity at the lowest level.
You might want to change DB content manually(i.e upgrades to new version)
You might forget to check some constrain in your code.
You can look at this like client/server validation. Your program is client, db is server. Mostly client validation is enough, but you must have server validation just in case something goes wrong.
I think a data person would say both are absolutely necessary. Your question assumes that your middle tier application code will be in front of that database now and forever.
The truth is that middle tier applications come and go, but data lives forever.
There's no getting away from length of columns in schema design. I think you're asking if it's a good practice for the middle tier to enforce them. Maybe not, but they're key for the database.
Often when you declare a set of columns to be unique, it's something that you will want to query by - so it should most likely be indexed anyway.
Yes your application should do the appropriate checking, but what if a mistake slips through? If your database knows something is meant to be unique, at least you know you won't store invalid data (or not "badly" invalid data, like duplicates of data intended to be unique). At any rate you could ask the opposite question: what does it cost you?
Constraints, both unique and foreign not only enforce data integrity, but they have performance implications. Without knowledge of these 'informal' constants, database optimizers are going to make unpredictable decisions about how to execute your statements.
If you want to have bad data, then take off the unique constraints in the database. I have worked around databases since the 1970's and have queried or imported data stored in hundreds of databases. I have never seen good data in databases where the constraints are improperly set at the application level. Many things other than the application hit the database (imports from other systems, a quick update to prod data to fix a data issue that is run from the query window, other applications, etc.). Many times the application is replaced and the constraints are lost.
but still our application has to check during user registration whether the user input is valid (unique) or not, without that and only with the DB constraint we will simply get an error.
That's the funniest thing I've ever read. You simply get an error? That's really all you need right? Ever heard of error trapping? Try Catch ring any bells?
It's actually very counter productive for your app to "check". The database is going to check anyways on the constraint so why do it twice. The app should just insert the row as if it will be fine. The DB will check for uniqueness and raise an error on a failure... You app should CATCH that error and do whatever you were doing in your existing check.
Yes. Just catch the key violation error and the key constraint will have done the work for you without having to incur an additional overhead of an additional check first.