Re: Unique NULLs?

From: PaulCinVT <paulcinvt_at_aol.com>
Date: 08 Dec 1999 19:20:41 GMT
Message-ID: <19991208142041.15996.00000032@ngol07.aol.com>

In article <384e9ba3.7131023_at_super.news-ituk.to>, johng_at_mm.com (TurkBear) writes:

>...in any
>case, a NULL cannot be unique, since NULL is not comparable to any other
>value
>even itself - A unique field, by definition in Oracle, is a NOT NULL field,
>so
>you cannot have one with NULL as its value...

Not quite accurate...

From Oracle Documentation...

Combining UNIQUE Key and NOT NULL Integrity Constraints UNIQUE key constraints allow the input of nulls unless you also define NOT NULL constraints for the same columns. In fact, any number of rows can include nulls for columns without NOT NULL constraints because nulls are not considered equal to anything. A null in a column (or in all columns of a composite UNIQUE key) always satisfies a UNIQUE key constraint.

Columns with both unique keys and NOT NULL integrity constraints are common. This combination forces the user to enter values in the unique key and also eliminates the possibility that any new row's data will ever conflict with an existing row's data.

---

---

Note:
Because of the search mechanism for UNIQUE constraints on more than one column, you cannot have identical values in the non-null columns of a partially null composite UNIQUE key constraint.  

Paul in VT Received on Wed Dec 08 1999 - 13:20:41 CST