Re: Updating Primary Key

Vinod Bhansali <iiug@hotmail.com> 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? Why would you want to change the order number once it's in the system? Unless you're using preprinted order forms and not assigning order numbers. I would think that would create a record keeping problem. We always required a cancel and reissue. Anyway, if you will be doing this sort of thing it would be better to use a serial field in the Order table to join to Item and then it wouldn't matter what you did to your order number. Although, to my mind, data integrity is more than just not orphaning child records. >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 > carlos Currently on hiatus from unemployment. ---------------------- Do you do Linux? :) Get your FREE @linuxstart.com email address at: http://www.linuxstart.com