Custom Constraint

Hi All,

I need to create a custom constraint (or a trigger?) on a table, and could
use some help. What I have is a table with a user_name (varchar) column
and an active (int 1) column. What I want to do is to enforce unique user
names, but only for an active user. That is, there can be n rows with the
same user name as long as the value in the active column is 0. There can
be only one row with that same user name, however, where the active value
is 1.

So what I'm thinking of is something like this:

CREATE TABLE usr(
    id           NUMERIC(10, 0)    NOT NULL
                                   CONSTRAINT pk_usr__id PRIMARY KEY,
    login        VARCHAR(128)      NOT NULL
                                   CONSTRAINT con_usr__login
                       SELECT CASE WHEN (SELECT COUNT(*)
                                         FROM   usr
                             WHERE  login = login
                             AND    active = 1) > 0
                                              THEN false ELSE true END,
    active       NUMERIC(1, 0)     CONSTRAINT ck_usr__active CHECK (active IN (1,0))
                                   DEFAULT 1
);

Now, I know that this constraint won't work (it won't even compile, of
course), but it gives the general idea. I'm not familiar enough with the
constraint syntax to know right off the bat what is the best way to do it.
If anyone can make suggestions or point me to some examples that will
help, I would geatly appreciate it.

Best,

David

--
David Wheeler                                         AIM: dwTheory
David@Wheeler.net                                     ICQ: 15726394
                                                   Yahoo!: dew7e
                                                   Jabber: Theory@jabber.org