Are unique constraints on the DB necessary?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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?