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(at)Wheeler(dot)net ICQ: 15726394
Yahoo!: dew7e
Jabber: Theory(at)jabber(dot)org