Z-Car

MySQL – Constraints on Hierarchical Data in a Self-Referential Table

blonde-woman-working-on-her-laptop-from-home-5

I recently had a client who was using a table structure that utilized a self-referential structure using an ID field and Parent_ID fields. After upgrading to Innodb, his DBA was trying to utilize a foreign key constraint to perform cascading UPDATE and DELETE statements. After much heart-ache, they called me in to find out why it was not working. Unfortunately, I had to share with them the fact that MySQL does not support this type of use of constraints on self-referential tables.

This deviation from SQL standards results affects an ON UPDATE CASCADE or ON UPDATE SET NULL that recurses to update the same table it has previously updated during the cascade. Instead of cascading, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations in MySQL. According to the Innodb developers, this is to prevent infinite loops resulting from cascaded updates, although I would think this could be addressed in future versions. A self-referential ON DELETE SET NULL, on the other hand, is still possible, as is a self-referential ON DELETE CASCADE.


Where I have been – cars that I have owned over the years

lotus in a field

These are some of the cars I have owned in the past, starting with the first car I ever owned.

1976 Datsun 710
1970 Datsun 240Z
1987 Toyota MR2
1988 Toyota Celica All-Trac
1972 Datsun 240Z
1991 Eagle Talon
1992 Mazda Miata
1991 Toyota MR2 Turbo
1994 Mazda RX-7
1968 Datsun Roadster
1970 Datsun 240Z
1976 Datsun 260Z
1998 BMW M3
1997 Mazda Miata
1993 Mazda RX-7 CYM
2003 Mazda Miata
1987 Toyota SDK8
2005 Lotus Elise
2006 Infiniti G35x
1997 Ford F150
2011 Infiniti M37x
1994 Mazda Miata

I have also owned a couple Tundra’s, T100, and Troopers mixed in there as well.  What was my favorite car?  I love my current Lotus Elise, however my 1993 RX-7 CYM was probably my all time favorite.  The Talon was most reliable, it went over 150K miles, very few of them were what you would call “easy” miles.  My 2006 Infiniti cleared 170K miles before finally giving up one of its cylinders.