Subject Re: [firebird-support] Altering Primary Key on Table
Author Helen Borrie
At 04:45 AM 1/09/2005 +0000, you wrote:
>I need to change the sort order from ascending to descending on the
>Primary Key for an (empty) table.


>Have tried (Alter Index/ Drop
>Index) using IBExpert (Pers) and EMS Interbase both of which
>generate SQL errors.
>According to the IB 6 Lang Ref:
>Alter Index
>"Alter Index Fails if Index is defined for a UNIQUE, PRIMARY KEY or
>FOREIGN KEY constraint .. to alter such an index, use DROP INDEX to
>delete the index etc."
>Drop Index
>"Important- You cannot drop system-defined indexes, such as those
>So, how does one do this with an FB (1.5) Table? Must one drop the
>table then re-create from scratch or is there an alternative 'work-

Well, if you drop the constraint. Obviously, you will have to
drop any referencing foreign key constraints first.

But what do you think you win by reversing the order of the PK index? The
PK index does not confer any "sort order" on a table. It's just like any
other ascending index - will provide an order only if you query the table
with an ORDER BY on that particular column.

If you want to be able to do a speedier ORDER BY PKColumn DESC, just create
a descending index on the column.