Re: How to create unique constraint on NULL columns

On Fri, 2005-07-15 at 13:46 +0300, Andrus wrote:
> I have table
>
> CREATE TABLE test( col1 CHAR NOT NULL, col2 CHAR,
> UNIQUE (col1, col2) );
>
> This table allows to insert duplicate rows if col2 is NULL:
>
> INSERT INTO test VALUES ( '1', NULL );
> INSERT INTO test VALUES ( '1', NULL );
>
> does NOT cause error!

The two rows are not duplicated. NULL means "any value" in that context,
so you can only say that the values for the first column are equal.
Nothing can be said about the values in the second column. The database
can't say they are the same, and can't say they are different either.

http://en.wikipedia.org/wiki/Null_%28SQL%29

> How to create constraint so that NULL values are treated equal and second
> insert is rejected ?

I think you can do that with special operators (such as IS DISTINCT
FROM) but you're using NULL as a normal value, that is not what it's
meant to be in the first place, and I advice to be careful:

http://www.postgresql.org/docs/8.0/interactive/functions-comparison.html

please read the paragraphs on ... = NULL, IS NULL, and IS DISTINCT FROM.

NULL is meant to represent the lack of knowledge ("unknown"). If you are
ever treating NULL as a real value (i.e. comparing it to other values or
or other NULLs), you must think twice about your design. IMVHO, the only
place for IS NULL and IS DISTINCT FROM are meta-operations on data,
administrative tasks and so on. There should be no need to use them in
"normal" queries.

Unless you're coding quick and dirty hacks when you really know what
you're doing but don't care about the correctness of your design, of
course.

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo(at)ESI(dot)it