Re: Changing the value of a primary key

Andreas Peykan-Sepahi wrote: > > Hello, > > Is there a simple way to change a primary key of a table without > creating a new record, updating the foreign key columns and then > deleting the old record? > Does Informix provide a standard procedure to handle this kind of > problems? > > Database: Informix Online 7.2x > Yes if you have a logged database: 1) BEGIN WORK 2) SET CONSTRAINTS ALL (or the name of the primary or foreign key constraint) DEFERRED 3) UPDATE keys (the order of updates will depend on what you choose to defer, if you defer the primary key constraint you must update the master record first, if the foreign key constraint then the children must be updated first, if 'ALL' then the order does not matter but other constraints are also deferred). 4) COMMIT/ROLLBACK WORK If a CONSTRAINT violation still exists at the time the COMMIT is executed the transaction will automatically be rolled back. Art S. Kagel