Mike,
> "Technical Note: as of PostgreSQL 7.2.1, DEFERRABLE UNIQUE
> constraints are
> not supported -- which means that you should not declare unique
> constraints
> on columns whose values will be frequently re-arranged, like custom
> sort
> order columns, even if the values in these columns are unique."
>
> Perhaps you mean for columns that are part of an order by clause Or
> you mean
> something much more subtle...where the value for a column is set to A
> then to
> B then to C... all the time?
The latter. For example, if you have a table like:
create table articles ( article_id INT, title VARCHAR(100), summary varchar(300), content TEXT, sort_order INT );
... where sort_order is a user-maintained custom sort order which is
different from the alpha sort.
In this case, the values in sort_order will be unique whenever
re-ordering is complete, and this is fairly easy to ensure via
triggers. However, while the re-ordering is in process, there will
temporarily be some non-unique values while, for example, the
article.sort_order #122 is being swapped with article.sort_order #123.
When deferrable unique constraints are implemented (maybe Postgres
8.0?) then the uniqueness check will happen only at the *end* of a
transaction, and a unique index on this kind of a column will be
permitted. Currently, it raises an error preventing column
re-ordering.
-Josh Berkus