You probably didn't quite understand the suggestion. You should create a
unique index like:
create unique index your_index_name on your_table (companyID,
associationID) where ysnDefault;
This will restrict the uniqueness checks to the records where ysnDefault
is true (and not null, of course).
HTH,
Csaba.
On Tue, 2003-11-04 at 17:39, 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
> I want to prevent this from happening, but still allow multiple
> company10 association7 false
> company10 association7 false
> entries for example.
>
> The idea of using NULLs is a good idea, but this is a production
> database and would require changes to the web-based front end (7 of
> them), not to mention each database. That's why I want to change the
> behaviour to only allow one unique company<-->association<-->TRUE
> combination. Right now there are a number of companies which have
> multiple default associations in the database, so I am going to have to
> back-trace and find out which association is actally the correct default.
>
> BTW I am using postgresql 7.3.4
>
> Ron
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend