how to create unique automatic increment field?

I have this table:

Column Name                    Null?    Type


------------------------------ -------- ----


TEMPID                         NOT NULL NUMBER(38)
DISPLAYORDER                            NUMBER(38)
NAME                                    VARCHAR2(100)

I want displayorder field to (as the name implies) decide in which order records will be displayed using ORDER BY clause. This field has UNIQUE constraint.

Now, let's say this table has following 5 records: TEMPID DISPLAYORD NAME
---------- ---------- ----------

        20          1 1st record
        21          2 2nd record
        22          3 3rd record
        23          4 4th record
        24          5 5th record

Now I want to change the last record (5th record) to be displayed first, and hence change displayorder field to 1. I can't just do a regular update since this will violate the unique constraint. Even without the unique constraint update would not give me wanted results, since now I would have two records with value of 1.

Is there a way
in oracle to automatically change the other values "up"? So, if I change the display order of 5th record to 1, the results would be: TEMPID DISPLAYORD NAME
---------- ---------- ----------

        20          2 1st record
        21          3 2nd record
        22          4 3rd record
        23          5 4th record
        24          1 5th record

Something like automatic increment that would still guarantee the uniqueness of the field. Do I need to use a trigger (on update of displayorder find other lower or equal values and increment them by one) or is there a different way?

thanks for any help

--

If the human brain were so simple
that we could understand it,
we would be so simple we couldn't.
-Makes Sense... don't it?

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Mar 31 2000 - 14:03:06 CST