Re: Custom Constraint

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