re: [Fwd: Foreign key ... ON UPDATE CASEADE]
On Monday 31 March 2003 02:37, vinita Vigine Murugiah wrote: I have been use mysql ver 3.23.53, also I'm using on DELETE CASECADE. It seems working fine. I believe UPDATE CASECADE is working from version 4.0.8. --- -- I have following problem (purchaseOrder_items::orderNum reference purchaseOrder_items; ::orderNum) select * from purchaseOrder; +--+-++---++ | orderNum | orderSupplierID | orderDate | orderNote | orderTotal | +--+-++---++ | 123 | k1 | 2003-03-26 | testing |250 | +--+-++---++ 1 row in set (0.00 sec) mysql select * from purchaseOrder_items; +--++---+--+---+ | orderNum | itemID | itemList | itemCost | itemDelivDate | +--++---+--+---+ | 123 | book1 | equipment | 100 | 2003-03-26| | 123 | book2 | equp | 150 | 2003-03-26| +--++---+--+---+ 2 rows in set (0.00 sec) mysql update purchaseOrder_items set orderNum=567 where orderNum=123; ERROR 1216: Cannot add a child row: a foreign key constraint fails I'm not sure if it's permitted. mysql mysql mysql update purchaseOrder set orderNum=567 where orderNum=123; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails It worked fine for me on 4.0.12. So, upgrade is recommended. I can NOT edit the orderNum. It seems the only way to edit the orderNum is, delete the children (purchaseOrder_items) or move to a tem table, then edit the parent orderNum and add back the children with the new orderNum, This is to many transactions. Is there any better way to edit foreign key values Of course I can switch to the a upper mysql version(4.0.8), if that is the only solution, which one is recommendable?? Thanks -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Foreign key ... ON UPDATE CASEADE]
Original Message Subject: Foreign key ... ON UPDATE CASEADE Date: Thu, 27 Mar 2003 12:08:34 +1100 From: vinita Vigine Murugiah [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Hello I have been use mysql ver 3.23.53, also I'm using on DELETE CASECADE. It seems working fine. I believe UPDATE CASECADE is working from version 4.0.8. - I have following problem (purchaseOrder_items::orderNum reference purchaseOrder_items; ::orderNum) select * from purchaseOrder; +--+-++---++ | orderNum | orderSupplierID | orderDate | orderNote | orderTotal | +--+-++---++ | 123 | k1 | 2003-03-26 | testing |250 | +--+-++---++ 1 row in set (0.00 sec) mysql select * from purchaseOrder_items; +--++---+--+---+ | orderNum | itemID | itemList | itemCost | itemDelivDate | +--++---+--+---+ | 123 | book1 | equipment | 100 | 2003-03-26| | 123 | book2 | equp | 150 | 2003-03-26| +--++---+--+---+ 2 rows in set (0.00 sec) mysql update purchaseOrder_items set orderNum=567 where orderNum=123; ERROR 1216: Cannot add a child row: a foreign key constraint fails mysql mysql mysql update purchaseOrder set orderNum=567 where orderNum=123; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails -- I can NOT edit the orderNum. It seems the only way to edit the orderNum is, delete the children (purchaseOrder_items) or move to a tem table, then edit the parent orderNum and add back the children with the new orderNum, This is to many transactions. Is there any better way to edit foreign key values Of course I can switch to the a upper mysql version(4.0.8), if that is the only solution, which one is recommendable?? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]