Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 1F2B74761C2
	for <pgsql-sql@postgresql.org>; Fri, 27 Sep 2002 14:29:40 -0400 (EDT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id 40128476E8A
	for <pgsql-sql@postgresql.org>; Fri, 27 Sep 2002 14:29:38 -0400 (EDT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id F1281D625; Fri, 27 Sep 2002 11:29:34 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id E6CF75C02; Fri, 27 Sep 2002 11:29:34 -0700 (PDT)
Date: Fri, 27 Sep 2002 11:29:34 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: GB Clark <postgres@vsservices.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Constraint problems
In-Reply-To: <20020927131547.09ed1af2.postgres@vsservices.com>
Message-ID: <20020927112807.D42421-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/389
X-Sequence-Number: 9666

On Fri, 27 Sep 2002, GB Clark wrote:

> CREATE TABLE sysusers (
> user_id			INTEGER DEFAULT nextval('sysusers_user_id_seq')
> 					UNIQUE NOT NULL PRIMARY KEY,
> username			text NOT NULL UNIQUE,
> password			text NOT NULL,
>
> createby			text NOT NULL DEFAULT 'syscreate',
>
> status			char(1) DEFAULT 'A' CHECK (status in ('A', 'I')),
> comment1			text,
> comment2			text,
> tstamp			timestamp DEFAULT CURRENT_TIMESTAMP
> );
>
> CREATE UNIQUE INDEX username_unique_idx ON sysusers (username);
>
> INSERT INTO sysusers (username, password, createby) VALUES ('syscreate', 'testing', 'syscreate');
>
> ALTER TABLE sysusers ADD CONSTRAINT createby_test CHECK (createby in (username));

This is only going to check the value of username in the current row.

Why not use a foreign key from createby to username?