foreign keys and data integrity in mysql

tbach2

I started out learning SQL with MySQL, and I've found it to very useful... But I'm realizing that other db's might make my job a little easier.

I designed an online course CMS which allows professors to design their own courses-- complete with lectures, tests, discussion, chat, email, etc. But it's really hard to keep up with the maintenance... deleting courses and students was not my first concern, and now I'm petrified of the task ahead of me: I already know that there are orphan records in some of the tables 🙁

Mysql obviously does not enforce referential integrity in the myisam tables (and I've just about exhausted my knowledge of the subject with that statement).

Do the innodb tables do that?

Can mysql help me prevent orphan records?

Any recommended articles about data integrity (or, IOW, what in the heck is data integrity, anyway)?

cap97

Not sure I get what you're saying. When you delete a course, there's students, articles, essays, etc. attached to that course, that are left behind? If that's it, all you gotta do in your admin is make sure, when you delete said course, that the script searches and deletes entries from other tables pertaining to that course.


$sql = "DELETE * FROM course WHERE CourseID = 1";
$result = mysql_query($sql,$db);

$sql = "DELETE * FROM students WHERE CourseID = 1";
$result = mysql_query($sql,$db);

$sql = "DELETE * FROM articles WHERE CourseID = 1";
$result = mysql_query($sql,$db);

echo "The course and all its relevant data have been deleted from the database!";

If that's not what you're talking about, forgive me. I took a shot.

Sxooter

You want cascading on delete triggers, postgresql and (I think) interbase / phoenix both have them.

tbach2

cap97, that's what I'm doing now; but that gets to be an awful lot of recursive deletes. 'course' is one big object, and I have a lot of delete methods that call other delete methods depending on where they are in the hierarchy. Problem is, I didn't have the foresight to build the delete methods while I had my relations mapped out and in front of me. (phpMyAdmin's PDF ability might have been real handy for that...)

What strikes me is that OOP may not be the most efficient way to accomplish recursive deletes-- and if I forget to delete a dependant record, the database just happily lets me make orphans. That doesn't seem right.

Another problem is that not every table has 'course_id' in it, since they are in 4th normal form. sigh

sxooter, one of your previous posts is what got me thinking down this line. Triggers and stored procedures are still greek to me, but it looks like I could have the database do a lot of the checking that I'm doing manually (ie, if I delete course_id, then everything dependant upon it must be deleted too.) It sounds like cascading on delete triggers is exactly what I'm looking for. Assuming these example tables/relationships:

Course
course_id
course_name
etc

Unit
unit_id
course_id
unit_name
etc

Test
test_id
unit_id
test_title
etc

Test_section
section_id
test_id
section_name
etc

would a cascading on delete trigger allow me to just say "DELETE FROM Course WHERE course_id='1'" and have it recursively delete items in Test_section? That would be really handy in the threaded discussion...

or, would it prevent me from doing so until all the dependant records are deleted?

at any rate, I'm intrigued...

chriskl

You don't need anything fancy - just proper foreign keys (which MySQL still does not have). In PostgreSQL, you go:

CREATE TABLE parent (foo integer primary key);

and then:

CREATE TABLE child (bar integer REFERENCES parent(foo) ON DELETE CASCADE);

And then whenever you delete a row from the parent table, all rows that relate to it in the child table will also be deleted.

I highly suggest not using MySQL if you want to have any guarantee of data integrity...

Chris

Sxooter

Sorry if I made it sound more complex than it is. The kinds of triggers I'm talking about are mostly all built-ins for postgresql. I.e. you type a couple of keywords and poof your triggers are built.

Don't forget serial:

CREATE TABLE parent (foo SERIAL primary key);

then the rest of Chriskl's post will give you autoincrementing fields as well.

As for Chriskl's jab at mysql's integrity issues, I would say that if I could get a version that ONLY ran on innodb tables, I might use it here and there, but honestly, the only thing that has me on the "warpath" as regards MySQL is their slimy GPL / commercial license ploy. I.e. GPLing the connect libs in 4.x so you HAVE to buy a commercial license to use it with any commercial software you write. It actually makes it improper to link it into PHP by default now, since PHP's licensing isn't 100% compat with GPL, and you can't put one under the other. At least that's the feeling I got from the php mailing list postings about why it was pulled.

If it is technically improper to link MySQL to PHP with the GPL, the only compatible was to license even free use of PHP would technically be to buy a commercial license.

This is all new for MySQL 4.x, up through 3.x it was licensed LGPL allowing it to be linked properly.

chriskl

Hi Sxooter,

Yeah the postgres triggers are added using the REFERENCES keyword...I just thought it better to not scare the poor guy with triggers and stuff 🙂

Chris