Re: Constraint Problem

Ron St-Pierre wrote:

> 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?

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(at)Yahoo(dot)com #