X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id AC9904763DA for ; Tue, 6 May 2003 13:22:47 -0400 (EDT) Received: from wolff.to (wolff.to [66.93.249.74]) by postgresql.org (Postfix) with SMTP id BF18B475E77 for ; Tue, 6 May 2003 13:22:46 -0400 (EDT) Received: (qmail 21756 invoked by uid 500); 6 May 2003 17:23:54 -0000 Date: Tue, 6 May 2003 12:23:54 -0500 From: Bruno Wolff III To: Randall Lucas Cc: pgsql-sql@postgresql.org Subject: Re: UNIQUE boolean: only one row may be "true" Message-ID: <20030506172354.GA21510@wolff.to> Mail-Followup-To: Randall Lucas , pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.4i X-Spam-Status: No, hits=-38.6 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, QUOTE_TWICE_1,REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MUTT autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/94 X-Sequence-Number: 13259 On Tue, May 06, 2003 at 12:01:20 -0400, Randall Lucas wrote: > > Hi folks, > > After a late night of SQL hacking, my brain fizzed out: > > How shall I create a constraint such that only one row may be 'true'? > Rephrased, may I place a WHERE clause in a UNIQUE constraint, or > alternatively, may I use a CHECK constraint with an aggregate? You can't use a select in a check constraint. Using a partial index seems to do what you want. Do something like: create unique index thing_idx on thing (thing_group_id, is_main_thing) where is_main_thing = true; > > Example: > > CREATE TABLE thing ( > thing_id serial primary key, > thing_group_id int not null references thing_group(thing_group_id), > is_main_thing_p boolean not null default 'f', > -- there may be only one main thing per group: > unique (thing_group_id, is_main_thing_p='t') > -- or else something like: > -- check (count (*) from thing where thing_group_id=NEW.thing_group_id > and is_main_thing_p = 't' <2) > ); > > Best, > > Randall > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly