Re: table constraints - Mailing list pgsql-sql

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
>