MySQL relationships & constraints, should I use them?

Question

I've built a large database in MySQL and created all the relationships between the tables with SQLYog. That's all well and good but in my PHP site that I'm building I often have trouble when it comes to deleting rows from the database, I often run into referential integrity constraints. So then I try deleting data or setting the field to NULL from the linking tables first. I can usually get this to work with some trial and error but there seems to be no method or proper process in which I should do this. Should I be using the 'on delete set NULL or cascade delete settings'? I don't know which to use and worried it might delete important data in the other tables.

Do people even bother setting the relationship constraints in their databases? I mean it would seem a lot easier to do a delete then write some extra lines to update the other tables that were linked to it in the PHP code.

Any help would be much appreciated.

Answer 1

The database should be treated as its own fiefdom in that it should not trust the data outside itself. Too often I have seen systems where direct connectivity to the database was necessary and broke whatever rules were setup by the original application. Databases often morph into use by multiple applications and thus it is imperative that it implements its own data integrity. Assume that other developers will completely bypass your application or middle-tier. In addition, databases tend to stay in service far longer than the application that was originally created to service it.

Thus, yes, it is crucial to incorporate foreign key constraints, proper use of nullability constraints and so on in your database design. Those referential integrity constraints you are encountering are there to protect the data against orphaned rows. In addition, they provide documentation about how the tables relate to each other.

If you have a child entity which logically should be removed when the parent is removed then you could consider cascading deletes. I tend on the side of caution by not cascading deletes unless all the code that deletes the parent is already coded to delete the child in which case, you might as well cascade deletes. Cascade updates are obviously safer and there generally isn't a reason not to implement them unless you are worried about performance or are unable to implement them due to some restriction in the design.

Answer 2

If you need to control referential integrity (and I´d say you do in the vast majority of cases) it´s always better to let the DBMS to do the work for you.

Not only because there may be other applications (now or in the future) using the same data and you should not trust them to be as careful as you may be in your code but also for efficiency reasons.

A good DBMS will always check the constraints (e.g. checking that insertions of foreign keys hold a valid a value, i.e. a value that exists in the referenced table) faster than your code. Since this kind of checkings are the core of relational databases, DBMS are very optimized for thsi

Answer 3

It really depends on the type of data that you are storing, do you really need the data to have referential integrity? For most 'information system' this is a must.

Referring to your second paragraph:

I mean it would seem a lot easier to do a delete then write some extra lines to update the other tables that were linked to it in the PHP code.

Yes it is perhaps easier when you're only making a small scale system, but imagine that your application is successful, and you have more customers, more features needed to be implemented, more programmers needed, then you will inevitably forget to update/delete/insert relevant data to your database, that will be the moment that you'll understand that Codd wasn't joking when he said that

Integrity constraints must be specified separately from application programs and stored in the catalog. It must be possible to change such constraints as and when appropriate without unnecessarily affecting existing applications.

read up codd's rule here