> Hi all.
>
> I´ve the following SP:
>
> CREATE PROCEDURE SP_REINGRESAR_ITEM_PED_A_REQ (
> sucursal smallint,
> numero_pedido integer,
> numero_item integer)
> as
> declare variable codigo_producto integer;
> declare variable cantidad integer;
> begin
> select codigo_producto, cantidad_pedida from items_pedidos where
> sucursal = :sucursal and numero_pedido = :numero_pedido and
> numero_item = :numero_item into :codigo_producto, :cantidad;
> delete from items_pedidos where sucursal = :sucursal and
> numero_pedido = :numero_pedido and numero_item = :numero_item;
> if (not exists(select numero_pedido from items_pedidos where
> sucursal = :sucursal and numero_pedido = :numero_pedido and
> numero_item = :numero_item)) then
> begin
> update items_pedidos set numero_item = numero_item - 1 where
> sucursal = :sucursal and numero_pedido = :numero_pedido and
> numero_item > :numero_item;
> execute procedure sp_registrar_requerimiento :sucursal,
> :codigo_producto, 5, :cantidad;
> end
> end
>
> It works ok most of times, but rarely it gives following error:
> "Invalid insert or update value(s): object columns are
> constrained - no 2 table rows can have duplicate column values.
> violation of PRIMARY or UNIQUE KEY constraint "PK_ITEMS_PEDIDOS" on
> table "ITEMS_PEDIDOS".
> At procedure 'SP_REINGRESAR_ITEM_PED_A_REQ'.
>
> Error occurs at sentence:
> update items_pedidos set numero_item = numero_item - 1 where sucursal
> = :sucursal and numero_pedido = :numero_pedido and numero_item >
> :numero_item;
>
>
> Purpose of this SP is delete an item from items_pedidos and re-number
> existing items.
>
> Items_Pedidos:
> SUCURSAL SMALLINT,
> NUMERO_PEDIDO INTEGER,
> NUMERO_ITEM SMALLINT,
> // more fields
>
> PK_ITEMS_PEDIDOS: PRIMARY KEY (SUCURSAL, NUMERO_PEDIDO, NUMERO_ITEM)
>
> Any idea about how to isolate and fix this?
>
> Thanks!

a simple solution, don't use the PK field for this re-assignment and
ordering task.
First, the deleted records will not change the order of the PK fields. You
are merely trying to do some "housekeeping" by making your tables look like
it never had any gaps, no?
This is not necessary. If you must, just use another unconstrained field to
receive a value.
Alan