Hey Wayne,

I would say that you'd always want to check that the data meet the conditions you desire on the server-side (e.g php) and not on the database. You can of course do this if you want to, but I don't see a reason why you'd want to do checks after they have been sent to the database when you can do them before.

The only time you'd want to have the SQL perform any checks is when the manipulated data affects the relation between data (they are relational-databases after all). So if any piece of data has foreign dependencies, you would most definetly make the appropriate checks on the database-side and not server-side.

Let's say for example that you want to store in your database some famous actors and some of the movies that each actor has starred in. You could do this with the following two normalized tables:

Actor table: http://i.imgur.com/NaJmeYJ.png

Movies table: http://i.imgur.com/3tgonnT.png

The id column is just a unique identifier, and primary key for each table. actor_id is a foreign key and points to the id of the Actor table.

Now if you wanted to delete Jack Nicholson from your database, it would make sense to also delete all the movies you have stored about him. This would be cumbersome to do on the server-side as you would first have to do some complicated statements with Joins and stuff. Generally, things can get complicated fast if you don't design your table structure properly.

So it would be much better if you could tell the database to just delete the actor "Jack Nicholson" and let it handle the rest. This is where the advanced SQL functionality becomes useful and saves you from doing complicated SQL statements in your php. What you can do here is to give the "Movies" table Foreign Keys and Constraints. Like this:

ALTER TABLE Movies ADD CONSTRAINT fk_actor_id FOREIGN KEY (actor_id) REFERENCES Actor(id) ON DELETE CASCADE ON UPDATE CASCADE;

This will automatically delete and update the relevant data in the Movies table if their parent in the Actor table gets deleted or updated.

TL;DR

If there needs to be checks to ensure the structural integrity between data and the relation between them, you should program this on the SQL. For everything else, do it on php.