Constraint Problem

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

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 #

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