[soonerlandrun]

I'm trying to create a table that has a foreign key associating it with another table. I would like to use 'on update cascade' with the foreign key but can't get it to work.

I can use syntax like this:

fieldname int(11) Not Null References OtherTable(pkField) On Update Cascade

but, it simply doesn't work. You can insert records with invalid values in the foreign key field (meaning you can insert a value in this child table that doesn't exist in the table with the primary key) Also, you can have a valid value but when you delete the record with the primary key from the parent table the records in the child table with the foreign key are not deleted also, leaving you with some invalid foreign keys in the child table. (creating referential integrity problems)

Does MySQL support On Update Cascade? If so, can someone post some sample code so I can see the proper syntax to use.

Thanks.

[Pat Phelan]

MySQL doesn't always support DRI (Declarative Referential Integrity). The support depends on which MySQL store you use (MyISAM never supports DRI, Inno-DB usually supports it), and what version of MySQL you are using (newer versions support DRI better than older versions do).

-PatP

[soonerlandrun]

Thanks.

I'm using MySQL version 5.0.27.

I'm not sure which MySQL store I'm using.
I didn't install it an am not real familiar with it. Can I look in some config file somewhere and tell what MySQL store I have?

[r937]

do a SHOW CREATE TABLE and it should tell you

storage engine is assigned on a per table basis -- both the parent and child have to be InnoDB tables in order for CASCADE to work

furthermore, for the FK referencing column(s), there must also be an index where the referencing columns(s) are left-most in the index

[soonerlandrun]

It says, ENGINE=MyISAM

Forgive my ignorance, but in a nutshell, what is the difference between InnoDB and MyISAM. Why would you create one that is MyISAM if (as it appears) InnoDB has more functionality?

[r937]

myisam is a faster on SELECTs

FYI you can easily change the myisam table to innodb with ALTER TABLE

[Pat Phelan]

Like any good database tool, MySQL allows you to make choices about how you want it to do things for you. These choices are often critical for letting the DBA / developer make good choices for their data-management needs.

You've stumbled over what I consider to be an infuriating weakness in the present incarnation of MySQL... MySQL allows syntax to pass if there is any possibility that that syntax might be correct. It doesn't apply even basic checks to see if the underlying database engine can or will do what the syntax implies, so that commands will cheerfully be processed by the command processor even when there is no chance that those commands will ever have any affect! This flaw drives me crazy!

Because of this I think that you (as either a DBA or developer) need to really understand MySQL completely in order to be able to use it safely. The fact that the MySQL command interpreter will gleefully process a command without even raising a warning) that will never be processed makes it dangerous to use MySQL if you don't really understand the tool and pay careful attention to everything that you do with it. I don't think that kind of power tool should be left for people to play with until they at least issue a command to turn the warnings off (by that point they ought to understand that they are cutting down the safety net).

-PatP