Thanks Alan.

What this means: "the deleted records will not change the order of the
PK fields"? Shouldnt PK fields get updated at indexes as soon as they
have new values?

Regards

Alan McDonald escribió:

>> 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
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>