Are unique database restrictions necessary?

Question

I was interested lately. Suppose we make a webapp with JSF + Spring + JPA / Hibernate (or some other technology is good), and we can say that we have a "user" entity. We want the User to have a unique login. If we want to do this, we can put @UniqueConstraint in the "Input" column, but still, our application must check during user registration whether the user’s input is valid (unique) or not, without this and only with a DB restriction we just get a mistake. Does it make me think that DB constraints are really necessary / useful? The only thing I can think of when they will give us any advantages is when someone tries to hack us (but I think that our application should be proof of SQL injection in any case) or we try to manually change the contents of the database data (which should not really happen). In fact, now that I am thinking about it, are there DB limitations in general necessary / good practices? Like string length etc.

Answer 1

For me, absolutely yes , see Database as a forex from Dan Chak of 97 Thinks that every software architect Must know. He says it is much better than he could.

Answer 2

Yes they are. They provide data integrity at the lowest level.

You might want to manually change the contents of the database (for example, updating to a new version)

You may forget to check some restrictions in the code.

You can look at this as a client / server check. Your program is a client, db is a server. Most often, checking the client is enough, but you should have a server check in case something goes wrong.

Answer 3

I think a person with data would say that this is absolutely necessary. Your question suggests that your mid-tier application code will be in front of this database now and forever.

The truth is, mid-tier applications come and go, but the data lives on forever.

It is not possible to get away from the length of the columns in the schema. I think you are asking if it is good practice for the intermediate level to enforce them. Maybe not, but they are key to the database.

Answer 4

Often, when you declare a set of columns unique, this is what you want to query, so it will most likely be indexed.

Yes, your application should perform an appropriate check, but what if the error slips? If your database knows that something means uniqueness, at least you know that you will not store invalid data (or not bad data, such as duplicate data that must be unique). In any case, you could ask the opposite question: what does it cost you?

Answer 5

If you want to have bad data, then remove the unique restrictions on the database. I have been working with databases since 1970 and have requested or imported data stored in hundreds of databases. I have never seen good data in databases where restrictions were set incorrectly at the application level. Many things, except the application, get into the database (import from other systems, quick update of prod data to fix a data problem that starts from the query window, other applications, etc.). Many times the application is replaced and restrictions are lost.

Answer 6

Constraints, both unique and external, not only ensure data integrity, but also have performance implications. Without knowledge of these “informal” constants, database optimizers will make unpredictable decisions on how to execute your statements.

Answer 7

but still, our application should check during user registration whether the user input is valid (unique) or not, without this and only with the database we will just get an error.

This is the funniest thing I've ever read. Are you just getting an error message? Is that really all you need? Have you ever heard of catching bugs? Try catch ring any bells?

Actually it is very inefficient for your application to "check". The database will check the restriction anyway, so why do it twice. The application should just insert a line as if everything would be fine. The database will check for uniqueness and cause an error if it fails ... The application should remove this error and do what you did in your existing scan.

Answer 8

Yes. Just catch the key violation error, and a keyword restriction would do your job without having to add extra overhead for additional verification.