RE: Updating Primary Key

What version are you running? I assume you have a logged database. Did you check the error code on set constraints all deferred? I successfully did what you are trying to do with set constraints deferred on 7.30uc2 Will >===== Original Message From "Vinod Bhansali" <iiug@hotmail.com> ===== >Sujit, > >set constraints for order disabled; >set constriants for item disabled; > >When I execute above stmts it gives error : >892: Cannot disable object (informix.pkeyorder) due to other active objects> >If I change there sequence as below : >set constriants for item disabled; >set constraints for order disabled; > >It gives me syntax error for the following stmt. : >UPDATE order SET orderno = 2 where orderno = 1;> > >Thnaks >Vinod Bhansali > > > >>From: Sujit.Pal@bankofamerica.com >>CC: informix-list@iiug.org >>Subject: Re: Updating Primary Key >>Date: Wed, 31 May 2000 13:28:12 -0700 >> >> >> >>Vinod >> >>For a delete, you need to delete the children first, then the parent. >>For an insert, you need to insert the parent first, then the children. >> >>Considering that an update could be looked at as a delete followed by an >>insert, >>you are stuck between a rock and a hard place when trying to figuring out >>the >>order in which you would update the keys, preserving the constraints. >> >>I had this problem recently and I had to defer to the DBA (he has resource >>priviledges) in order for him to disable the constraints and reenable them >>after >>the update. You will need to disable the PK in the parent (order) and then >>the >>FK in the child (item) and reenable them in the reverse order after the >>update. >> >>set constraints for order disabled; >>set constriants for item disabled; >>-- do your update >>UPDATE order SET orderno = 2 where orderno = 1; >>UPDATE item SET orderno = 2 where orderno = 1;>>-- >>set constraints for item enabled; >>set constraints for order enabled; >> >>HTH >>Sujit >> >> >> >> >> >> >>Vinod Bhansali <iiug@hotmail.com> on 05/31/2000 12:37:02 PM >> >>To: informix-list@iiug.org >>cc: >>Subject: Updating Primary Key >> >> >> >>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? >> >>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 : >> >>{ 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 >>________________________________________________________________________ >>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com >> >> >> >> >> > >________________________________________________________________________ >Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com ------------------------------------------------------------ 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/ ------------------------------------------------------------