Thread: table constraints

table constraints

From
"Casey T. Deccio"
Date:
Hi,

I am running PostgreSQL 7.4.7.  I am having some issues with a
constraint for one of my database tables.  The code snippet below
outlines the code and its output (output is commented).

In this case each bldg has an owner associated to it, and each animal
lives in some bldg.  Each owner has exactly one own favorite animal out
of all the bldgs owned by him.  So the constraint added to each zoo row
is that the boolean field 'favorite' is true for exactly once for each
group of animals in the zoo that have a common owner.

The unique_favorite(text) function is created to help with this
constraint.  The function returns what it is supposed to (see select
statement in the code), but doesn't hold when used as a constraint.

There may be a better way to do this, but I'm not looking for that right
now.  I would like to know if I am using this constraint wrong, or if
there is there something wrong with the table constraints in the
database system.

Thanks,
Casey

------ Code snippet -------
CREATE TABLE owner (owner varchar(50));
-- CREATE TABLE
INSERT INTO owner VALUES ('steve');
-- INSERT 13193166 1

CREATE TABLE bldg (bldg varchar(50), owner varchar(50));
-- CREATE TABLE
INSERT INTO bldg VALUES ('bldg1', 'steve');
-- INSERT 13193169 1

CREATE TABLE zoo (animal varchar(50), bldg varchar(50), favorite
boolean);
-- CREATE TABLE

CREATE OR REPLACE FUNCTION unique_favorite(text) RETURNS BOOLEAN AS '
DECLARE temp RECORD;
BEGIN SELECT into temp * FROM (SELECT b.owner, sum(CASE WHEN favorite = TRUE THEN 1 ELSE 0 END) AS
num_favorites   FROM (SELECT * FROM zoo WHERE animal = $1) z INNER JOIN bldg b
USING(bldg) GROUP BY b.owner) sub WHERE sub.num_favorites <> 1; RETURN NOT FOUND;
END;
' LANGUAGE plpgsql;
-- CREATE FUNCTION

ALTER TABLE zoo ADD check(unique_favorite(animal));
-- ALTER TABLE

INSERT into zoo VALUES ('monkey', 'bldg1', false);
-- INSERT 13193173 1
-- (This shouldn't be allowed!!!)

SELECT *, unique_favorite(animal) FROM zoo;
--  animal | bldg  | favorite | unique_favorite
-- --------+-------+----------+-----------------
--  monkey | bldg1 | f        | f

INSERT into zoo VALUES ('monkey', 'bldg1', false);
-- ERROR:  new row for relation "zoo" violates check constraint "$1"

INSERT into zoo VALUES ('monkey', 'bldg1', true);
-- ERROR:  new row for relation "zoo" violates check constraint "$1"



Re: table constraints

From
Bruno Wolff III
Date:
On Mon, Feb 28, 2005 at 11:28:30 -0800, "Casey T. Deccio" <ctdecci@sandia.gov> wrote:
> 
> In this case each bldg has an owner associated to it, and each animal
> lives in some bldg.  Each owner has exactly one own favorite animal out
> of all the bldgs owned by him.  So the constraint added to each zoo row
> is that the boolean field 'favorite' is true for exactly once for each
> group of animals in the zoo that have a common owner.

One way to do this kind of thing is to add a UNIQUE key to the table
that holds the animal - table relation of the animal and table and add
a UNIQUE key for the building table of the building and owner.
Then in the owner table add favorite animal building and favorite
animal columns with the property NOT NULL. Then add two deferable
foreign keys (owner, favorite animal building) to the building table
and (favorite animal building, favorite animal) to the animal - building
table. When modifying data you want to change favorites before changing
the underlying tables (where a building or animal change affects someones
favorites).

P.S. in your example you used varchar(50) for the text strings. Using 'text'
is better unless there is a business rule limiting the name lengths to 50
or you expect to port the application to another rdbms.


Re: table constraints

From
"Casey T. Deccio"
Date:
On Mon, 2005-02-28 at 13:20 -0700, Bruno Wolff III wrote:
> On Mon, Feb 28, 2005 at 11:28:30 -0800,
>   "Casey T. Deccio" <ctdecci@sandia.gov> wrote:
> >
> > In this case each bldg has an owner associated to it, and each
> animal
> > lives in some bldg.  Each owner has exactly one own favorite animal
> out
> > of all the bldgs owned by him.  So the constraint added to each zoo
> row
> > is that the boolean field 'favorite' is true for exactly once for
> each
> > group of animals in the zoo that have a common owner.
> 
> One way to do this kind of thing is to add a UNIQUE key to the table
> that holds the animal - table relation of the animal and table and add
> a UNIQUE key for the building table of the building and owner.
> Then in the owner table add favorite animal building and favorite
> animal columns with the property NOT NULL. Then add two deferable
> foreign keys (owner, favorite animal building) to the building table
> and (favorite animal building, favorite animal) to the animal -
> building
> table. When modifying data you want to change favorites before
> changing
> the underlying tables (where a building or animal change affects
> someones
> favorites).
> 

Thanks for the input.  This may work in the example I've given.
However, the example I provided was contrived and was used merely to
show the discrepancy that I'm finding with using the function as a
constraint.  In the larger example, things are a bit more complex, and
I've found using such a constraint a better fit for now for the problem
I'm working with.

That said, I'd like to know why the constraint I provided isn't working
with the corresponding example.

> P.S. in your example you used varchar(50) for the text strings. Using
> 'text'
> is better unless there is a business rule limiting the name lengths to
> 50
> or you expect to port the application to another rdbms.

Thanks for the tip.  Good point.  I've never really known when to use
text over varchar, so though I began using varchar for potential later
porting to other rdbms, and since then it's just been tradition...:)

Casey



Re: table constraints

From
Andrew - Supernews
Date:
On 2005-02-28, "Casey T. Deccio" <ctdecci@sandia.gov> wrote:
> However, the example I provided was contrived and was used merely to
> show the discrepancy that I'm finding with using the function as a
> constraint.  In the larger example, things are a bit more complex, and
> I've found using such a constraint a better fit for now for the problem
> I'm working with.
>
> That said, I'd like to know why the constraint I provided isn't working
> with the corresponding example.

The CHECK is obviously being evaluated prior to the actual insertion of
the record, whereas the logic of your function clearly expects to be
evaluated after the insertion.

Allowing non-immutable functions in CHECK is probably an error, since it
can lead to tables which can not be dumped+restored (consider in your
example what happens when the constraint becomes false as a result of
deleting a row - at that point, a dump and restore of the table will fail,
since the constraint can not be defined after loading the data if it is
violated by that data).

Using triggers is a more reliable way to do this sort of thing - at least
then it is clear that you are checking the data only at the time of
modification, whereas CHECK constraints are declarative constraints which
are expected to be true at all times.

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services


Re: table constraints

From
"Casey T. Deccio"
Date:
On Mon, 2005-02-28 at 18:01 -0700, Andrew - Supernews wrote:
> The CHECK is obviously being evaluated prior to the actual insertion
> of
> the record, whereas the logic of your function clearly expects to be
> evaluated after the insertion.
> 

I finally came to that conclusion just a few minutes before I got this
email.  However, it was your email that made it quite clear why this
was.

> Allowing non-immutable functions in CHECK is probably an error, since
> it
> can lead to tables which can not be dumped+restored (consider in your
> example what happens when the constraint becomes false as a result of
> deleting a row - at that point, a dump and restore of the table will
> fail,
> since the constraint can not be defined after loading the data if it
> is
> violated by that data).

> Using triggers is a more reliable way to do this sort of thing - at
> least
> then it is clear that you are checking the data only at the time of
> modification, whereas CHECK constraints are declarative constraints
> which
> are expected to be true at all times.
> 

This is a very good point.  I've now implemented the check with a
trigger, and it works very well.  Thanks for the input and the logic.

Casey



Re: table constraints

From
"Greg Patnude"
Date:
foreign keys and primary keys have to be defined as unique at the table / 
column level if you want to implement a check constraint -- your contrived 
example doesn't stand up all that well -- If you want to use constraints --  
then your database schema should conform to traditional RDBMS theory and 
data normalization by having  primary and foreign keys instead of just 
trying to create arbitrary contraints on a non-normalized schema and 
implement constraints as a user-defined function...


""Casey T. Deccio"" <ctdecci@sandia.gov> wrote in message 
news:1109618910.32166.0.camel@boomerang.ran.sandia.gov...
> Hi,
>
> I am running PostgreSQL 7.4.7.  I am having some issues with a
> constraint for one of my database tables.  The code snippet below
> outlines the code and its output (output is commented).
>
> In this case each bldg has an owner associated to it, and each animal
> lives in some bldg.  Each owner has exactly one own favorite animal out
> of all the bldgs owned by him.  So the constraint added to each zoo row
> is that the boolean field 'favorite' is true for exactly once for each
> group of animals in the zoo that have a common owner.
>
> The unique_favorite(text) function is created to help with this
> constraint.  The function returns what it is supposed to (see select
> statement in the code), but doesn't hold when used as a constraint.
>
> There may be a better way to do this, but I'm not looking for that right
> now.  I would like to know if I am using this constraint wrong, or if
> there is there something wrong with the table constraints in the
> database system.
>
> Thanks,
> Casey
>
> ------ Code snippet -------
> CREATE TABLE owner (owner varchar(50));
> -- CREATE TABLE
> INSERT INTO owner VALUES ('steve');
> -- INSERT 13193166 1
>
> CREATE TABLE bldg (bldg varchar(50), owner varchar(50));
> -- CREATE TABLE
> INSERT INTO bldg VALUES ('bldg1', 'steve');
> -- INSERT 13193169 1
>
> CREATE TABLE zoo (animal varchar(50), bldg varchar(50), favorite
> boolean);
> -- CREATE TABLE
>
> CREATE OR REPLACE FUNCTION unique_favorite(text) RETURNS BOOLEAN AS '
> DECLARE
>  temp RECORD;
> BEGIN
>  SELECT into temp * FROM
>  (SELECT b.owner, sum(CASE WHEN favorite = TRUE THEN 1 ELSE 0 END) AS
> num_favorites
>    FROM (SELECT * FROM zoo WHERE animal = $1) z INNER JOIN bldg b
> USING(bldg) GROUP BY b.owner) sub
>  WHERE sub.num_favorites <> 1;
>  RETURN NOT FOUND;
> END;
> ' LANGUAGE plpgsql;
> -- CREATE FUNCTION
>
> ALTER TABLE zoo ADD check(unique_favorite(animal));
> -- ALTER TABLE
>
> INSERT into zoo VALUES ('monkey', 'bldg1', false);
> -- INSERT 13193173 1
> -- (This shouldn't be allowed!!!)
>
> SELECT *, unique_favorite(animal) FROM zoo;
> --  animal | bldg  | favorite | unique_favorite
> -- --------+-------+----------+-----------------
> --  monkey | bldg1 | f        | f
>
> INSERT into zoo VALUES ('monkey', 'bldg1', false);
> -- ERROR:  new row for relation "zoo" violates check constraint "$1"
>
> INSERT into zoo VALUES ('monkey', 'bldg1', true);
> -- ERROR:  new row for relation "zoo" violates check constraint "$1"
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 




Re: table constraints

From
"Casey T. Deccio"
Date:
On Tue, 2005-03-01 at 09:56 -0700, Greg Patnude wrote:
> foreign keys and primary keys have to be defined as unique at the
> table /
> column level if you want to implement a check constraint -- your
> contrived
> example doesn't stand up all that well -- If you want to use
> constraints -- 
> then your database schema should conform to traditional RDBMS theory
> and
> data normalization by having  primary and foreign keys instead of just
> trying to create arbitrary contraints on a non-normalized schema and
> implement constraints as a user-defined function...
> 

You are correct.  I did not take the time to write in these constraints
in the contrived example because I was rapidly trying to put together
something that would simply illustrate the problem.  They were/are in
place in my actual schema.  Thanks,

Casey