Re: Unique NULLs?

"W. Scott Moore" wrote:
> Is it possible to create a Unique field that can have one, and only one,
> null record in it?

NO
Each NULL value is different from another, but you can create a trigger on
a shadow field. This shadow field contains the same value as the original column,
but some insane value when the original column has a NULL value. You can then use a UNIQUE constraint on that column.

Starting with:
CREATE TABLE t ( n NUMBER ) ;

Something like:
REM Add a shadow column
ALTER TABLE t ADD ( shadow_n NUMBER );

REM Copy all the existing values
UPDATE t SET shadow_n = NVL(n, -99999);

REM Set the constraints
ALTER TABLE t MODIFY ( shadow_n UNIQUE NOT NULL );

REM Create the trigger that maintains the shadow value CREATE TRIGGER t1 ON INSERT OR UPDATE
BEGIN
        new.shadow_n = NVL(new.n, -99999)
END;
/

[p.s. the above was not tested and spelling may be incorrect]

Needless to say that the 'insane' value used here is '-99999'.

--
Erwin Dondorp
<http://www.dondorp.com/> Received on Wed Dec 08 1999 - 12:24:19 CST