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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
