Subject: table constraints
From: "Casey T. Deccio" <ctdecci@sandia.gov>
To: pgsql-sql@postgresql.org
Organization: Sandia National Laboratories
Date: Mon, 28 Feb 2005 11:28:30 -0800
Message-ID: <1109618910.32166.0.camel@boomerang.ran.sandia.gov>
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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"