Re: Constraint Problem

On Tue, 4 Nov 2003, Ron St-Pierre wrote:

> Greg Stark wrote:
>
> >Ron St-Pierre <rstpierre@syscor.com> writes:
> >
> >
> >
> >>This is not quite what I need. I need to create a constraint to allow only
> >>-one- of
> >>    company<->association<->default(=true) value
> >>but any number of
> >>    company<->association<->default(=false) values
> >>
> >>
> >
> >So a unique index on "(company,association) where default" doesn't do what you
> >want?
> >
> No it doesn't. For example, after I create the unique index I can still
> input:
>     company10     association7     true
>     company10     association7     true
>     company10     association7     true

You shouldn't be able to and I can't replicate similar behavior in a
simple test on 7.3.4.  I get "Cannot insert a duplicate key into unique
index" errors.

create table a(a int, b int, c boolean);
create unique index a_ind on a(a,b) where c;
insert into a values (1,1,true);
insert into a values (1,1,true);
insert into a values (1,1,false);
insert into a values (1,1,false);
insert into a values (1,2,true);

Where the second insert fails, but the others succeed.