Complicated unique constraint

I've got two tables:

create table person
(

   per_id number primary key,
   kerberos_id varchar2(30) unique
);

create table account
(

   system varchar2(20),
   username varchar2(20),
   per_id number references person
   constraint unique (system, username) );

There's a lot of other columns, but these are the relevant ones. I've got a unique composite key on account.system and account.username.

The person tables stores information about people: email address, social security number, and a kerberos id code assigned to them when they enter the school. For example, my kerberos id code is smithr01. The per_id primary key is just a sequence number.

The account table will be used to keep track of each individual computer account this person gets. The basic idea is that, as far as possible, computer account names will be the person's kerbeos_id, but there are, on occasion, reasons to have accounts with other names for special purposes, or because the system in question doesn't allow usernames of 8 alphanumerics.

account is a many-to-many table, since I can not only have accounts on many systems, but I can also have multiple accounts on the same system, with different account names. For example, I've got "roy" and "smithr01" accounts on several machines (this is ugly, but we it exists and we want the database to be able to represent it).

So, our idea was to use the convention that a null in account.username means to use the kerberos_id contained in the pointed-to record of the person table. If a particular account on a particular system isn't going to use the kerberos_id as the username, then we fill in the actual username in account.

The problem we now face is that the unique(system,username) constraint won't prevent us from having, for a user foo, two account records on the same system, one with username null, and the other with username having the string "foo". How can I write a constraint to disallow that?

--
Roy Smith <roy_at_popmail.med.nyu.edu>
New York University School of Medicine Received on Thu Feb 25 1999 - 09:12:25 CST