I’m working through the book called Build your own database driven website using php & mysql 4th edition. In chapter 7 - A content management system - it describes 3 ways to handle deleting authors, ie.
[LIST]
[]Prohibit users from deleting authors that are associated with jokes in the database.
[]When we delete an author, also delete any jokes attributed to the author.
[*]When we delete an author, set the authorid of any jokes attributed to the author
to NULL, to indicate that they have no author.[/LIST]
The book goes on to describe the code that handles the situation in the second point above, ie.
When we delete an author, also delete any jokes attributed to the author
As I’m adapting the code for the project I’m working on, I need to make it work for the first scenario, ie.
Prohibit users from deleting authors that are associated with jokes in the database
I’m not really sure how to go about this and wondered if anyone has any advice. I don’t think it’s covered in the book anywhere.
It really depends on how you’re dealing with this and which engine you’re using. I mean that MySQL uses 5 possible engines, one of them being InnoDB. This particular engine has the possibility of requesting referencial integrity by default in a relationship between two databases.
Referencial Integrity is just a term which means exactly what you need: if you have two tables with a one-to-many relationship, every record of the “many” part of the relationship needs to be linked with a record of the “one” part.
In your case, you have the authors listed in one table, and record implies a different author (your “one” part of the relationship). You also have many jokes, and plenty of them can have been written by the same author, hence the author id will appear in this table more than once (therefore, this is the “many” part of the relationship).
Referencial integrity only means that all jokes have to be related to an author and there can’t be any jokes with no author associated. You preserve the integrity of the information, so to say.
If you do it this way, you don’t need to worry about programming because the database itself will do the job.
This is not used very often because it slows down the database performance.
Most of the time, it is done by programming. How? Well, when someone clicks on the link to delete an author, you need to check first if there are any jokes related to this particular author. So you query the database to search for those jokes and, if the result of the query is bigger than 0 (that is, there is at least one record (=joke)), you then stop the deletion of the author and send a message back to the page to indicate that you can’t delete the author as he has jokes.
as you pointed out, a one-to-many relationship can be enforced in mysql only by InnoDB tables
however, referential integrity does ~not~ require that every row in the “many” table must be related to a row in the “one” table
if the foreign key is declared NULLable, the relationship is allowed to be optional
furthermore, “[relational integrity] is not used very often because it slows down the database performance” is completely wrong and a huge myth – database-enforced integrity is ~always~ faster than application-controlled integrity
please, all you conscientious new developers out there – don’t reinvent the wheel with application code
the database engine is better at handling database relationships than you are
Sorry Rudy, it was just a question, nothing else. I am not an DB expert. As a matter of fact, I am not an expert on any field, and I don’t pretend to be.
As far as I understand, and maybe I am doing a bad assumption here, InnoDB works as Microsoft Access would. According to Microsoft Access documentation (or my understanding of it :D), requesting Referencial Integrity slow down performance. With this I mean that Access has an option that you tick and then it activates the relationship constraint. My assumption was that InnoDB would have the same problem. After all, the default engine for MySQL is MyISAM.
With this I don’t mean that the integrity of the information should not be preserved, of course. I am only saying that asking the database to check the constrain has an impact on performace.
