Re: Constraint Problem - Mailing list pgsql-general

Ron St-Pierre wrote:

> Stephan Szabo wrote:
>
>>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.
>>
>>
>>
> You're right. When I run this the second insert fails. However, I
> modified my table to add the index and then successfully added an entry
> which should have failed. So I created a new table:
>         CREATE TABLE compass (
>           compassnID SERIAL PRIMARY KEY,
>           company int4 NOT NULL REFERENCES tblcompanies (cntcompanyid),
>           association int4 NOT NULL REFERENCES tblassociations
> (cntasncode),
>           ysnDefault bool
>         );
>
>         CREATE UNIQUE INDEX compassoc_default_ind ON compass
> (company,association) WHERE ysnDefault;
>
> and then inserted the data as:
>     INSERT INTO compass(company, association, ysnDefault) SELECT
> company, association, ysnDefault FROM oldCompAss;
>
> I then checked::
>     planrrontest=# SELECT * FROM compass WHERE company=23590;
>      compassnid | company | association | ysndefault
>     ------------+---------+-------------+------------
>            7777 |   23590 |           4 | t
>            8038 |   23590 |           2 | t
>            8040 |   23590 |           7 | t
>     (3 rows)
> And as you can see company 23590 has three default associations. Any
> ideas on how I can get around this?

That's what you complained about. I see different associations.

I guess you want only one row with ysndefault=true "per company", not
"per company+association". So the unique index has to be

     CREATE UNIQUE INDEX bla ON compass (company) WHERE ysnDefault;


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #