Re: Constraint Problem

Stephan Szabo wrote:

>On Tue, 4 Nov 2003, Ron St-Pierre wrote:
>
>
>
>>Greg Stark wrote:
>>
>>
>>
>>>Ron St-Pierre <rstpierre(at)syscor(dot)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