Re: Constraint Problem

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?

Thanks
Ron