Thread: 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

What would cause this trigger:

create trigger log_trigger
before insert on log
for each row
execute procedure update_host_table();

to only fire on the first insert per connection, but none of the subsequent inserts?

The trigger runs fine, the procedure it calls runs fine, but it only executes the
trigger once per connection.

Thanks,
Fran

On Thu, 10 May 2001, David Wheeler wrote:

> Hi All,
>
> I need to create a custom constraint (or a trigger?) on a table, and could
> use some help.
<snip />

To answer my own question, this is what I've come up with. To anyone who
happens to decide to entertain him/herself by looking this over: if you
happen to see any inefficiencies in how I'm doing this, and can tell me
how it might be done better, I would appreciate the comments! Here's my
solution:

--
-- TABLE: usr
--

CREATE TABLE usr(
    login        VARCHAR(128)      NOT NULL,
    active       NUMERIC(1, 0)     NOT NULL
                                   CONSTRAINT ck_usr__active CHECK (active IN (1,0))
                                   DEFAULT 1
);

--
-- FUNCTION: login_avil
--
-- This function is used by the table constraint ck_usr__login below to
-- determine whether the login can be used. The rule is that there can be any
-- number of rows with the same login, but only one of them can be active. This
-- allows for the same login name to be recycled for new users, but only one
-- active user can use it at a time.

CREATE   FUNCTION login_avail(varchar, int4) RETURNS BOOLEAN
AS       'SELECT CASE WHEN
                      (SELECT 1
                       FROM   usr
                       WHERE  $2 = 1
                              AND login = $1
                              AND active = 1) > 0
                 THEN false ELSE true END'
LANGUAGE 'sql'
WITH     (isstrict);

-- Now apply the constraint to the login column of the usr table.

ALTER TABLE usr ADD CONSTRAINT ck_usr__login CHECK (login_avail(login, active));

Thanks!

David

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

On Thu, May 10, 2001 at 11:18:56AM -0700, David Wheeler wrote:
> 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.

Might it be easier to have two tables? one for active users and one for
inactive users?  Don't know how you determine activity status, but if
you just want to "archive" formerly active users, it may be better to
keep them in separate tables and use an ON DELETE rule for the active
users table which does an insert into the inactive users table.  If the
user is to be reactivated, you'll still have the problem that the old
user name may conflict with a new user name.

What you're asking to do violates concepts of normalization.  I suspect
performance would be better with the two table approach as well.  I'm
guessing you must have additional information identifying users,
otherwise I don't see the point of having duplicate entries in the
inactive users table (unless you want to do statistics on name usage ;)

--
Eric G. Miller <egm2@jps.net>