*Jonathan Hedstrom* wrote
Yes, something like this:
CREATE UNIQUE INDEX index_name ON table_name ( unique_field) WHERE deleted=0;
My only concern here is
Note: The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly.
<http://www.postgresql.org/docs/8.1/interactive/indexes-unique.html>
Why scare us off?. The semantics of unique indexes vary from DBMS to DBMS.
For example, for IBM DB2:
When UNIQUE is used, null values are treated as any other values. For example,
if the key is a single column that may contain null values, that column may
contain no more than one null value.
<ftp://ftp.software.ibm.com/ps/products/db2/info/vr9/pdf/letter/en_US/db2s2e90.pdf>
But in PostgreSQL:
When an index is declared unique, multiple table rows with equal indexed values
will not be allowed. Null values are not considered equal.
<http://www.postgresql.org/docs/8.1/interactive/indexes-unique.html>
MySQL evinces both behaviors, depending on which storage engine a table uses.
An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL.
<http://dev.mysql.com/doc/refman/5.0/en/create-index.html>
So I guess if portability of the full semantics is an issue, be careful.
-- Lew
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
