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