Обсуждение: table constraints
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"
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.
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
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
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
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
>
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