Matthew Wilson wrote:
> I'm trying to comprehend how NULL values interact with unique indexes.
> It seems like I can insert two rows with NULL values in a column with a
> unique constraint just fine.
>
> Is there something special about NULL? Can anyone post some links to
> explain what is going on?
>
http://www.postgresql.org/docs/8.3/interactive/ddl-constraints.html#AEN2058
Last paragraph just above 5.3.4.
> What's the deal with NULL?
>
NULL = NULL is not true, it's null
NULL <> NULL is not false, it's null
It's the normal SQL 3 state logic (true/false/null) with only the true
value from the comparison causing the constraint violation. Think of
the unique constraint check like "does this value equal any other value
already recorded".
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au