pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Randall Lucas <rlucas@tercent.net>
To: pgsql-sql@postgresql.org
Subject: UNIQUE boolean: only one row may be "true"
Date: Tue, 6 May 2003 12:01:20 -0400
Message-ID: <FA25F27B-7FDB-11D7-A71E-000A957653D6@tercent.net> (raw)


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?

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




view thread (7+ messages)  latest in thread

Message-ID: <FA25F27B-7FDB-11D7-A71E-000A957653D6@tercent.net>
Permalink:  ../FA25F27B-7FDB-11D7-A71E-000A957653D6@tercent.net/
Also on:    postgresql.org/message-id/FA25F27B-7FDB-11D7-A71E-000A957653D6@tercent.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: rlucas@tercent.net
  Subject: Re: UNIQUE boolean: only one row may be "true"
  In-Reply-To: <FA25F27B-7FDB-11D7-A71E-000A957653D6@tercent.net>
  References: <FA25F27B-7FDB-11D7-A71E-000A957653D6@tercent.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox