Re: Updating Primary Key
Vinod
Sorry my disable sequence was incorrect. Since the PK has FKs hanging off it, it
cant be disabled without disabling the FKs.
>>UPDATE order SET orderno = 2 where orderno = 1;Saw your other message where it gives the error in char pos 8, thats order. Do
you think it is mistaking it for an ORDER as in ORDER BY? I have seen similar
problems with using other keywords as object names. Surprisingly it seems to be
doing the delete ok. Of course if it is the stores7 database you are working
with, my theory would be farfetched enough to be utterly implausible, since I
assume IFMX would have done enough regression testing to have caught this.
Sujit
Vinod Bhansali <iiug@hotmail.com> on 05/31/2000 01:46:38 PM
To: informix-list@iiug.org
cc: Sujit Pal@BOFA
Subject: 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