Обсуждение: Constraint Problem
I want to create a constraint that's a little different, but don't know how to implement it. I have an intermediate table with a compound PK and a boolean 'ysnDefault' column: comanyID INTEGER REFERENCES companies, assocationID INTEGER REFERENCES associations, ysnDefault BOOLEAN I just want to constrain the ysnDefault field to only allow one TRUE value for any companyID/associationID pair, with no restrictions on the number of FALSES. At first glance I thought it would be easy, but I can't see how to do it. Any suggestions? Thanks Ron
Ron St-Pierre <rstpierre@syscor.com> writes: > I just want to constrain the ysnDefault field to only allow one TRUE > value for any companyID/associationID pair, with no restrictions on the > number of FALSES. You could do that with a partial unique index. There is an example at the bottom of http://www.postgresql.org/docs/7.3/static/indexes-partial.html regards, tom lane
Tom Lane wrote: > Ron St-Pierre <rstpierre@syscor.com> writes: > >>I just want to constrain the ysnDefault field to only allow one TRUE >>value for any companyID/associationID pair, with no restrictions on the >>number of FALSES. > > > You could do that with a partial unique index. There is an example > at the bottom of > http://www.postgresql.org/docs/7.3/static/indexes-partial.html > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > Thanks Tom. That's exactly what I need. Ron
Tom Lane wrote: >Ron St-Pierre <rstpierre@syscor.com> writes: > > >>I just want to constrain the ysnDefault field to only allow one TRUE >>value for any companyID/associationID pair, with no restrictions on the >>number of FALSES. >> >> > >You could do that with a partial unique index. There is an example >at the bottom of >http://www.postgresql.org/docs/7.3/static/indexes-partial.html > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > 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 I've tried many different ALTER TABLE ... CREATE CONSTRAINT variations, all without success. Anyone know how? Thanks Ron
On Mon, 3 Nov 2003, Ron St-Pierre wrote: > Tom Lane wrote: > > >Ron St-Pierre <rstpierre@syscor.com> writes: > > > > > >>I just want to constrain the ysnDefault field to only allow one TRUE > >>value for any companyID/associationID pair, with no restrictions on the > >>number of FALSES. > >> > >> > > > >You could do that with a partial unique index. There is an example > >at the bottom of > >http://www.postgresql.org/docs/7.3/static/indexes-partial.html > > > > regards, tom lane > > > >---------------------------(end of broadcast)--------------------------- > >TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > 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 > > I've tried many different ALTER TABLE ... CREATE CONSTRAINT variations, > all without success. Wouldn't a unique constraint on those three fields do this? Nulls don't violate unique constraints. Does that work, or did I miss too much of this conversation?
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? -- greg
El Lun 03 Nov 2003 17:00, scott.marlowe escribió:
> >
> > 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
> >
> > I've tried many different ALTER TABLE ... CREATE CONSTRAINT variations,
> > all without success.
>
> Wouldn't a unique constraint on those three fields do this? Nulls don't
> violate unique constraints. Does that work, or did I miss too much of
> this conversation?
Scott, he want's to have as much falses as can be in the association field,
but only one true.
--
El mejor sistema operativo es aquel que te da de comer.
Cuida tu dieta.
-----------------------------------------------------------------
Martin Marques | mmarques@unl.edu.ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
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
I want to prevent this from happening, but still allow multiple
company10 association7 false
company10 association7 false
entries for example.
The idea of using NULLs is a good idea, but this is a production
database and would require changes to the web-based front end (7 of
them), not to mention each database. That's why I want to change the
behaviour to only allow one unique company<-->association<-->TRUE
combination. Right now there are a number of companies which have
multiple default associations in the database, so I am going to have to
back-trace and find out which association is actally the correct default.
BTW I am using postgresql 7.3.4
Ron
You probably didn't quite understand the suggestion. You should create a unique index like: create unique index your_index_name on your_table (companyID, associationID) where ysnDefault; This will restrict the uniqueness checks to the records where ysnDefault is true (and not null, of course). HTH, Csaba. On Tue, 2003-11-04 at 17:39, 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 > I want to prevent this from happening, but still allow multiple > company10 association7 false > company10 association7 false > entries for example. > > The idea of using NULLs is a good idea, but this is a production > database and would require changes to the web-based front end (7 of > them), not to mention each database. That's why I want to change the > behaviour to only allow one unique company<-->association<-->TRUE > combination. Right now there are a number of companies which have > multiple default associations in the database, so I am going to have to > back-trace and find out which association is actally the correct default. > > BTW I am using postgresql 7.3.4 > > Ron > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend
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.
Ron St-Pierre <rstpierre@syscor.com> writes:
> 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
> I want to prevent this from happening, but still allow multiple
> company10 association7 false
> company10 association7 false
> entries for example.
For example:
test=# create table test (company integer, association integer, isdefault boolean);
CREATE TABLE
test=# create unique index testi on (company,association) where isdefault;
ERROR: syntax error at or near "(" at character 30
test=# create unique index testi on test (company,association) where isdefault;
CREATE INDEX
test=# insert into test values (10,7,true);
INSERT 6888594 1
test=# insert into test values (10,7,true);
ERROR: duplicate key violates unique constraint "testi"
test=# insert into test values (10,7,false);
INSERT 6888596 1
test=# insert into test values (10,7,false);
INSERT 6888597 1
test=# select * from test;
company | association | isdefault
---------+-------------+-----------
10 | 7 | t
10 | 7 | f
10 | 7 | f
(3 rows)
--
greg
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
On Tue, Nov 04, 2003 at 10:25:03AM -0800, Ron St-Pierre wrote: > Stephan Szabo wrote: > 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 as you can see company 23590 has three default associations. Any > ideas on how I can get around this? Well, shouldn't the index be CREATE UNIQUE INDEX compassoc_default_ind ON compass (company) WHERE ysnDefault; ? -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) Essentially, you're proposing Kevlar shoes as a solution for the problem that you want to walk around carrying a loaded gun aimed at your foot. (Tom Lane)
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 > 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? But they're all different associations. If you reread your original question above you'll see that's not what you described. You said you only wanted one of any <company,association,default> value when default is true. Try just create unique index on compass (company) where ysndefault -- greg
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 >>>>>> >>>>>> > > > >>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? >> >> > >But they're all different associations. If you reread your original question >above you'll see that's not what you described. You said you only wanted one >of any <company,association,default> value when default is true. > >Try just > >create unique index on compass (company) where ysndefault > > You're right, I was trying to associate the company-association-default where default=TRUE, when I didn't need the association as part of it at all. It's working now, thanks. I'll now try to find my way out of 'Monday morning mode'. Thanks all Ron
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 #
Ron St-Pierre <rstpierre@syscor.com> writes: > Greg Stark wrote: > > >Try just > > > >create unique index on compass (company) where ysndefault You might also want create unique index on compass (company,association) If a company isn't supposed to have duplicate associations at all. This would also prevent having a default and non-default relationship to the same association. -- greg