Re: Deleted Flag/Unique Constraint

*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