Re: Updating Primary Key

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. |/ ////////| +----------------------+-----------------------------------+-----------+