Re: Constraint Problem - Mailing list pgsql-general
From | Ron St-Pierre |
---|---|
Subject | Re: Constraint Problem |
Date | |
Msg-id | 3FA7EEFF.6060404@syscor.com Whole thread Raw |
In response to | Re: Constraint Problem (Stephan Szabo <sszabo@megazone.bigpanda.com>) |
Responses |
Re: Constraint Problem
(Alvaro Herrera <alvherre@dcc.uchile.cl>)
Re: Constraint Problem (Greg Stark <gsstark@mit.edu>) Re: Constraint Problem (Jan Wieck <JanWieck@Yahoo.com>) |
List | pgsql-general |
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
pgsql-general by date: