Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Posts
    7

    Unanswered: On Update Cascasde with MySQL?

    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:

    Code:
    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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Nov 2007
    Posts
    7
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2007
    Posts
    7
    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?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    myisam is a faster on SELECTs

    FYI you can easily change the myisam table to innodb with ALTER TABLE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •