Re: Updating Primary Key

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

Hi, I am very new to informix. I have come from Oracle world. Is there any way we can create sequence in infromix and used it for the id (for example user_id). Please help me. Thank you very much. Prabha Sent via Deja.com http://www.deja.com/ Before you buy.

The 9.3 product will support oracle-like sequences. Until then you will need to use the serial data type which functions much like the MS Access autonumber column. pchapp@sapient.com wrote: > Hi, > > I am very new to informix. I have come from Oracle world. Is > there any way we can create sequence in infromix and used it for the id > (for example user_id). Please help me. > > Thank you very much. > > Prabha > > Sent via Deja.com http://www.deja.com/ > Before you buy. -- Madison Pruet =========================================== Enterprise Replication Product Developement Dallas, Texas Informix Software ===========================================

Use data type SERIAL for this. It is an interger value. You can define the initial value when create the table. Clifton Bean <pchapp@sapient.com> wrote in message news:8h75j1$r2i$1@nnrp1.deja.com... > Hi, > > I am very new to informix. I have come from Oracle world. Is > there any way we can create sequence in infromix and used it for the id > (for example user_id). Please help me. > > Thank you very much. > > > Prabha > > > Sent via Deja.com http://www.deja.com/ > Before you buy.