Re: Unique constraint over 2 columns (one of them nullable)

Bram Pouwelse wrote:
> Hi all,
>
> I wonder if it's possible to enforce uniqueness of a combination of 2
> columns where one of the 2 is nullable.
>
> Example:
>
> ACCOUNT
> id
> name
> passwd
> email
> anonymousgroup_id NULLABLE
>
> ANONYMOUSGROUP
> id
> name
>
> this is a simple representation of the problem ;) The accounts are used
> for returning customers but there is an option not to use the returning
> customer option and fill out your details everytime you come back.
>
> So there are 2 situations
> 1. Returning customer must have a unique emailaddress (has no
> anonymousgroup_id)
> 2. Not returning customer the mailadress + anonymousgroup_id combination
> must be unique
>
> Because in oracle a null value is always unique the mailadresses of the
> returnig customer can be non unique.
>
> Is there an other way to get oracle to enforce the uniqueness of the
> mail address?
>
> Thanks in advance
>
> Bram

Bram,

Isn't this the same issue that was recently discussed in the thread "Unique constraint over 2 columns with allowable NULLs"? This same newsgroup, so it shouldn't be that hard to find.

Groet,

Ruud de Koter. Received on Fri Dec 07 2007 - 01:22:18 CST