Re: table constraints

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(at)sandia(dot)gov> wrote in message
news:1109618910(dot)32166(dot)0(dot)camel(at)boomerang(dot)ran(dot)sandia(dot)gov(dot)(dot)(dot)
> 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
>