Re: Updating Primary Key
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