Re: Constraint Problem - Mailing list pgsql-general

From Csaba Nagy
Subject Re: Constraint Problem
Date
Msg-id 1067965160.30526.60.camel@coppola.ecircle.de
Whole thread Raw
In response to Re: Constraint Problem  (Ron St-Pierre <rstpierre@syscor.com>)
List pgsql-general
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


pgsql-general by date:

Previous
From: Network Administrator
Date:
Subject: Re: PostgreSQL v7.4 Release Candidate 1
Next
From: Vivek Khera
Date:
Subject: Re: PostgreSQL v7.4 Release Candidate 1