*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
toenforce 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
containnull 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
notconsidered 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
applyto NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values
forcolumns 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
