Should validation of unique fields be handled on database level?

Question

I am writing quite a large web application for my BSc project and am writing it in PHP using MySQL.

When creating the SQL tables should I use unique fields? The reason I ask this is because I am writing validation methods into my PHP. For example, if I wanted to check if an email address already exists in the database I would first make an SQL statement seeing if any email addresses are returned and then insert the initial SQL data into the database.

If I was to just insert the initial SQL data it would throw an error on the entire statement because email addresses must be unique.

Is this the correct way of approaching validation?

Answer 1

Well...these are two entirely different things. By setting contraints (like unique) on columns in the database you can prevent a wrong dataset to be inserted into the database. That's basic stuff to ensure data consistency. By adding validation to your application you add another layer. While preventing that wrong datasets will be inserted into the database, you mainly create means to tell the user what he did wrong.

You need both of those. And in general unique constraints should not be the only things taking care of your database. It's recommended to also use foreign key constraints and if applicable more complex data validations as well (custom constraints). However, as you are using MySQL not a lot of that is really available to you

Just go for as much as you can get, because in the end it will prevent a lot of headaches trying to figure out why a particular error occurred until you finally realize that the data in your database is wrong. That can really be a pain.