Thread: partial unique constraint
Trying to come up with the proper syntax to meet the following criteria:
create table foo (bar integer, baz boolean UNIQUE (bar, baz = true));
note the above syntax is not correct, but should demonstrate what i'm
trying to do; I want to add a unique constraint such that we only allow
one case of bar and baz = true... i can have unlimited bar and baz =
false, and there can be multiple bar and baz = true if the bars are
different... did some doc reading and mail list searching but a valid
syntax for this seems to be escaping me...
btw I'm pretty sure I could do this with an external trigger, but am
wondering about a constraint oriented approach
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Robert Treat <xzilla@users.sourceforge.net> writes:
> Trying to come up with the proper syntax to meet the following criteria:
> create table foo (bar integer, baz boolean UNIQUE (bar, baz = true));
The correct way to do it is with a separate CREATE INDEX command:
create table foo (bar integer, baz boolean);
create unique index fooi on foo (bar) where baz = true;
Personally I'd spell that last as just "where baz" ...
regards, tom lane
O kyrios Robert Treat egrapse stis Apr 6, 2004 : > Trying to come up with the proper syntax to meet the following criteria: > > create table foo (bar integer, baz boolean UNIQUE (bar, baz = true)); You could simply leave your table as is. A null vale on baz (equivalent of 'false') will have no impact on the constraint. A 't' value (equivalent to 'true') will enforce the constraint. In other words you can convert your problem as baz == null <==> baz='false' baz == 't' <==> baz='true' (you must never use baz='f') > > note the above syntax is not correct, but should demonstrate what i'm > trying to do; I want to add a unique constraint such that we only allow > one case of bar and baz = true... i can have unlimited bar and baz = > false, and there can be multiple bar and baz = true if the bars are > different... did some doc reading and mail list searching but a valid > syntax for this seems to be escaping me... > > btw I'm pretty sure I could do this with an external trigger, but am > wondering about a constraint oriented approach > > Robert Treat > -- -Achilleus
You should create a functional index here.
Ries
> -----Oorspronkelijk bericht-----
> Van: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]Namens Robert Treat
> Verzonden: dinsdag 6 april 2004 16:30
> Aan: pgsql-sql@postgresql.org
> Onderwerp: [SQL] partial unique constraint
>
>
> Trying to come up with the proper syntax to meet the
> following criteria:
>
> create table foo (bar integer, baz boolean UNIQUE (bar, baz = true));
>
> note the above syntax is not correct, but should demonstrate what i'm
> trying to do; I want to add a unique constraint such that we
> only allow
> one case of bar and baz = true... i can have unlimited bar and baz =
> false, and there can be multiple bar and baz = true if the bars are
> different... did some doc reading and mail list searching but a valid
> syntax for this seems to be escaping me...
>
> btw I'm pretty sure I could do this with an external trigger, but am
> wondering about a constraint oriented approach
>
> Robert Treat
> --
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
On Tue, 6 Apr 2004, Robert Treat wrote: > Trying to come up with the proper syntax to meet the following criteria: > > create table foo (bar integer, baz boolean UNIQUE (bar, baz = true)); > > note the above syntax is not correct, but should demonstrate what i'm > trying to do; I want to add a unique constraint such that we only allow > one case of bar and baz = true... i can have unlimited bar and baz = > false, and there can be multiple bar and baz = true if the bars are > different... did some doc reading and mail list searching but a valid > syntax for this seems to be escaping me... > > btw I'm pretty sure I could do this with an external trigger, but am > wondering about a constraint oriented approach You can't precisely with a constraint (AFAIR sql is marginally limited in that way at least in 92 and 99), but you should be able to do it with a partial unique index something likecreate unique index foo_i on foo(bar) where baz is true;
On 06/04/2004 15:29 Robert Treat wrote: > Trying to come up with the proper syntax to meet the following criteria: > > create table foo (bar integer, baz boolean UNIQUE (bar, baz = true)); > > note the above syntax is not correct, but should demonstrate what i'm > trying to do; I want to add a unique constraint such that we only allow > one case of bar and baz = true... i can have unlimited bar and baz = > false, and there can be multiple bar and baz = true if the bars are > different... did some doc reading and mail list searching but a valid > syntax for this seems to be escaping me... > > btw I'm pretty sure I could do this with an external trigger, but am > wondering about a constraint oriented approach What about create table foo (bar integer, baz boolean); create unique index foo_bar_baz on foo(bar, baz) where baz = true; -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
On Tue, 2004-04-06 at 10:29, Robert Treat wrote:
> Trying to come up with the proper syntax to meet the following criteria:
>
> create table foo (bar integer, baz boolean UNIQUE (bar, baz = true));
Tt takes 2 steps.
CREATE TABLE ...
CREATE UNIQUE INDEX ... (bar) WHERE baz = true;
--
Rod Taylor <rbt [at] rbt [dot] ca>
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc
On Tue, 2004-04-06 at 11:17, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > Trying to come up with the proper syntax to meet the following criteria:
> > create table foo (bar integer, baz boolean UNIQUE (bar, baz = true));
>
> The correct way to do it is with a separate CREATE INDEX command:
I think I had initially abandoned looking at that type of solution after
having run across this paragraph in the docs while looking for the
proper constraint syntax:
"Note: The preferred way to add a unique constraint to a table is ALTER
TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique
constraints could be considered an implementation detail that should not
be accessed directly."
The subliminal mind is a powerful force eh? Perhaps that paragraph
should be modified... but I'm not sure if it should expanded to include
thoughts along the lines of Stephan's response or maybe just drop the
"should not be accessed directly" bit...
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On Tuesday 06 April 2004 17:38, Robert Treat wrote: > On Tue, 2004-04-06 at 11:17, Tom Lane wrote: > > Robert Treat <xzilla@users.sourceforge.net> writes: > > > Trying to come up with the proper syntax to meet the following > > > criteria: create table foo (bar integer, baz boolean UNIQUE (bar, baz = > > > true)); > > > > The correct way to do it is with a separate CREATE INDEX command: > > I think I had initially abandoned looking at that type of solution after > having run across this paragraph in the docs while looking for the > proper constraint syntax: > > "Note: The preferred way to add a unique constraint to a table is ALTER > TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique > constraints could be considered an implementation detail that should not > be accessed directly." > > The subliminal mind is a powerful force eh? Perhaps that paragraph > should be modified... but I'm not sure if it should expanded to include > thoughts along the lines of Stephan's response or maybe just drop the > "should not be accessed directly" bit... Anyone know if there is any way to do this in one of the later SQL standards? The CREATE INDEX thing is a bit of a hack, and I certainly wouldn't have thought of it either. -- Richard Huxton Archonet Ltd