Alan McDonald escribió:

>> Alan:
>>
>> Alan McDonald escribió:
>>
>>>> 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
>>>>
>>>>
>>> once a record is deleted, the value of the PK is gone. The
>>>
>> index will, of
>>
>>> course be updated, but the order of the records does not change
>>>
>> (barring the
>>
>>> now omitted record).
>>> i.e. before delete, PK values 1, 2, 3, 4, 5, 6 etc. Now delete
>>>
>> record PK=3.
>>
>>> new order is 1, 2, 4, 5, 6 etc i.e. the order has not changed.
>>> Alan
>>>
>>>
>> That´s what I´ve tought. If after delete PK=3, I want 4 -> 3, 5 -> 4,
>> etc, why doesn´t "always" work? (It works most of times!).
>>
>> --
>> Jorge Andrés Brugger
>>
>
> my first argument is: what's the point if the order doesn't change?
> I suspect you are using this field as something user wants to manipulte and
> if it's also part of a compound PK, then that's trouble IMHO.
>
> Your original select
> 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;
>>>
>
> does not use anr order by clause. so you are not necessarily getting these
> records back in any specific numero_item order.
> think logically, to make the change you are wanting - (numero_item - 1) ,
> you have to guarantee that you are not changing values to existing values.
> so you need to at least order by numero_item ascending.
>

You have a point!! I haven't even noted that situation.And work most of
times because creation order is secuencial, so renumbering also was,
except a new lower item is placed last ....

> I still think you design is poor based on your need change (shuffle) PK
> values of a table. IMHO PK values are imutable once created. Are you using
> these compound fields as something meaningful to your users? That may be the
> root of your problem. Are you really sure that a compound PK is a good idea?
> I would argue that there is no need for it. Let the user change the value if
> numero_item to anything they want as long as it's not part of a PK.
>

It´s something like a temp table, with items which have to be
correlative. Then, if the user deleted one of those items, all
subsecuent should be renumbered to keep the secuence without holes. Is
was logical for me create a doc_number + item_number PK :)

Thanks for your help.

> 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
>
>
>
>
>