Re: Updating Primary Key

"Mark D. Stock" wrote: > > Vinod Bhansali 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? > > No. Unless you also update the items. > > > 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 : > > It doesn't matter. If your constraints are active, then you cannot > update the order, because it would leave 'orphan' items, and you cannot > update the items because there is no order. > > You have to defer the constraints, as you have done in your example, > using the SET CONSTRAINTS command. That's the DEFERRED option, NOT the DISABLED option! Disabling constraints is a DBA function, deferring them is an application function. Deferring them simply means they are checked at the COMMIT WORK command, instead of after each SQL command. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |This email will self-destruct in |/// / ////| | |10 sec. If you received this email |// / /////| | |in error, sorry about the mess. |/ ////////| +----------------------+-----------------------------------+-----------+