Thread: Constraint Problem

Constraint Problem

From

Ron St-Pierre

Date:

02 November 2003, 16:38:13

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

Re: Constraint Problem

From

Tom Lane

Date:

02 November 2003, 16:48:49

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

Re: Constraint Problem

From

Ron St-Pierre

Date:

03 November 2003, 01:47:03

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

Re: Constraint Problem

From

Ron St-Pierre

Date:

03 November 2003, 17:35:07

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

Re: Constraint Problem

From

"scott.marlowe"

Date:

03 November 2003, 19:14:47

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?

Re: Constraint Problem

From

Greg Stark

Date:

03 November 2003, 19:14:51

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

Re: Constraint Problem

From

Martin Marques

Date:

03 November 2003, 19:23:06

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

Re: Constraint Problem

From

Ron St-Pierre

Date:

04 November 2003, 15:39:42

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

Re: Constraint Problem

From

Csaba Nagy

Date:

04 November 2003, 15:59:56

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

Re: Constraint Problem

From

Stephan Szabo

Date:

04 November 2003, 16:41:16

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.

Re: Constraint Problem

From

Greg Stark

Date:

04 November 2003, 16:46:41

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

Re: Constraint Problem

From

Ron St-Pierre

Date:

04 November 2003, 17:25:36

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

Re: Constraint Problem

From

Alvaro Herrera

Date:

04 November 2003, 18:03:50

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)

Re: Constraint Problem

From

Greg Stark

Date:

04 November 2003, 18:11:08

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

Re: Constraint Problem

From

Ron St-Pierre

Date:

04 November 2003, 18:31:14

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

Re: Constraint Problem

From

Jan Wieck

Date:

04 November 2003, 18:52:03

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 #

Re: Constraint Problem

From

Greg Stark

Date:

04 November 2003, 19:00:13

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