Thread: UNIQUE boolean: only one row may be "true"
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
Can you elaborate a little on what you want to do with this (as in, example data, problem domain, etc.)? This is probably not the answer you're looking for, but without knowing more, I would guess that you need a different design, not a 1-true,many false field constraint. Perhaps you want something like this? Table A: thing_groupid (primary key) ...other group info... Table B: thing_groupid (reference to A.thing_groupid, many to 1) thing_id ...details about thing... ? MT --- Randall Lucas <rlucas@tercent.net> 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? > > 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 __________________________________ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com
On Tue, May 06, 2003 at 12:01:20 -0400, Randall Lucas <rlucas@tercent.net> 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
> How shall I create a constraint such that only one row may be 'true'? Instead of, or along with your pg boolean, use a C type boolean where true is 1 and false is null: ... col_tf int check( col_tf = 1 || col_tf is null ) unique, ... a kludge to be sure, but works __________________________________ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com
Hi Michael, Per Bruno's suggestion, I think I will go with a partial index. But your question brings up a good point and I am happy to hear what you have to say on the design. The problem being solved is essentially replicating a very limited subset of SQL DDL to describe an external tabular dataset. For a given dataset (thing_group) consisting of a number of columns (thing), there must be no more than one column which is a primary key (is_main_thing_p). Do you have any suggestions on how this might better be implemented? It seems to me that with a partial index, there is not too much overhead and it doesn't seem too offensively un-boycecoddean. Best, Randall On Tuesday, May 6, 2003, at 01:13 PM, Michael Teter wrote: > Can you elaborate a little on what you want to do with this (as in, > example > data, problem domain, etc.)? > > This is probably not the answer you're looking for, but without > knowing more, > I would guess that you need a different design, not a 1-true,many > false field > constraint. > > Perhaps you want something like this? > > Table A: > thing_groupid (primary key) > ...other group info... > > Table B: > thing_groupid (reference to A.thing_groupid, many to 1) > thing_id > ...details about thing... > > ? > > MT > > --- Randall Lucas <rlucas@tercent.net> 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? >> >> 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 > > > __________________________________ > Do you Yahoo!? > The New Yahoo! Search - Faster. Easier. Bingo. > http://search.yahoo.com >
Randall Lucas <rlucas@tercent.net> writes:
> Do you have any suggestions on how this might
> better be implemented? It seems to me that with a partial index, there
> is not too much overhead and it doesn't seem too offensively
> un-boycecoddean.
FWIW, I'd go with the unique partial index too. It's a very efficient
solution. But it's not portable :-(. I'd be interested to hear what
people would do to solve this problem in bog-standard SQL.
regards, tom lane
Tom, > FWIW, I'd go with the unique partial index too. It's a very efficient > solution. But it's not portable :-(. I'd be interested to hear what > people would do to solve this problem in bog-standard SQL. I do it by creating a trigger (on a INSERT or UPDDATE trigger) which call's a pgsql function. Not very eficcient but I don't have that many inserts for the tables on a regular base (100 a day at the max...) Ries > -----Oorspronkelijk bericht----- > Van: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]Namens Tom Lane > Verzonden: woensdag 7 mei 2003 5:01 > Aan: Randall Lucas > CC: Michael Teter; pgsql-sql@postgresql.org > Onderwerp: Re: [SQL] UNIQUE boolean: only one row may be "true" > > > Randall Lucas <rlucas@tercent.net> writes: > > Do you have any suggestions on how this might > > better be implemented? It seems to me that with a partial > index, there > > is not too much overhead and it doesn't seem too offensively > > un-boycecoddean. > > FWIW, I'd go with the unique partial index too. It's a very efficient > solution. But it's not portable :-(. I'd be interested to hear what > people would do to solve this problem in bog-standard SQL. > > regards, tom lane > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) >