Unique constraint over 2 columns (one of them nullable)

From: Bram Pouwelse <bram_at_pouwelse.com>
Date: Thu, 06 Dec 2007 23:41:50 +0100
Message-ID: <878e9$47587aae$53510224$23570@cache120.multikabel.net>

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 Received on Thu Dec 06 2007 - 16:41:50 CST