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