RE: Updating Primary Key

From what I understand set constraints deferred only lasts as long as the transaction, so it would have to be within the transaction. Will >===== Original Message From "Mark D. Stock" <mdstock@mydas.freeserve.co.uk> ===== >Vinod Bhansali wrote: >> >> I have to tables order and item. Order is parent table and Item is child >> table. Both the tables have primary keys. Item table has a foreign key which >> references the Primary key in the Order table. >> >> Can I update the primary key in Order table for a order which has four items >> related to it in Item table? > >No. Unless you also update the items. > >> I finally want to update the orderno in both the tables. I am not whether I >> should update parent first or child first. I also tried deferring the >> constraints, but where I update from Item table it gives SQL error 691 and >> ISAM error 111. When I try to update Order table it gives syntax error.>> >> Followed are the table structure and the SQL stmts which I gave : > >It doesn't matter. If your constraints are active, then you cannot >update the order, because it would leave 'orphan' items, and you cannot >update the items because there is no order. > >You have to defer the constraints, as you have done in your example, >using the SET CONSTRAINTS command. I have a feeling however, that it >should be before the BEGIN WORK command. > >> { TABLE "informix".order row size = 48 number of columns = 4 index size = 12 >> } >> create table "informix".order >> ( >> orderno integer, >> cust char(20), >> city char(20), >> amt integer, >> primary key (orderno) constraint "informix".pkeyorder >> ) extent size 16 next size 16 lock mode page; >> >> { TABLE "informix".item row size = 28 number of columns = 3 index size = 54 >> } >> create table "informix".item >> ( >> orderno integer, >> item char(20), >> qty integer, >> primary key (orderno,item) constraint "informix".pkeyitem >> ) extent size 16 next size 16 lock mode page; >> alter table "informix".item add constraint (foreign key (orderno) >> references "informix".order constraint "informix".fkeyitem); >> >> insert into order values(1,"cust","city",100); >> insert into item values (1,"item1",1); >> insert into item values (1,"item2",2); >> insert into item values (1,"item3",3); >> insert into item values (1,"item4",4); >> select * from order; >> select * from item;>> begin work; >> set constraints all deferred; >> UPDATE order SET orderno = 2 where orderno = 1; >> UPDATE item SET orderno = 2 where orderno = 1;>> commit work; >> >> Thanks in advance >> Vinod Bhansali > >Cheers, >-- >Mark. > >+----------------------------------------------------------+-----------+ >| Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| >| http://www.informix.com http://www.informixhandbook.com |///// / //| >| http://www.iiug.org +-----------------------------------+//// / ///| >| |This email will self-destruct in |/// / ////| >| |10 sec. If you received this email |// / /////| >| |in error, sorry about the mess. |/ ////////| >+----------------------+-----------------------------------+-----------+ ------------------------------------------------------------ This e-mail has been sent to you courtesy of OperaMail, as a free service from Opera Software, makers of the award-winning Web Browser, Opera. Visit us at http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail account is waiting at: http://www.operamail.com/ ------------------------------------------------------------