Integrity problem on 7.3.4 - Mailing list pgsql-novice

I have a table 'pages' with a foreign key constraint on another table,
'photo'. The idea is that one should not be able to insert a
non-existant photo into a page. If I try to do this from the pgsql
prompt the insert fails as it should. However if I run a function, one
is able to add a row with an arbitrary n_photo_id (I discovered this
after I added this test arbitrarily to my unit test regime). The
relevant snippet of the function is below, together with the table
definitions.

Thanks for any help,
Rory

---------------------------------------------------------------------

    CREATE OR REPLACE FUNCTION
        fn_p2_edit_page (integer, integer, integer, integer, integer,
                         integer, varchar, varchar, integer) RETURNS INTEGER
    AS '
    DECLARE
        userid        ALIAS for $1;
        pageid        ALIAS for $2;
        styleid       ALIAS for $3;
        photoid       ALIAS for $4;
        soundid       ALIAS for $5;
        pageno        ALIAS for $6;
        titletext     ALIAS for $7;
        storytext     ALIAS for $8;
        pagelength    ALIAS for $9;
        recone        RECORD;
        newpageival   INTERVAL;
        newpagetime   TIME    := ''00:00:00'';
        newphotoval   INTEGER := NULL;
        newsoundval   INTEGER := NULL;
        newpageno     INTEGER := 0;
    BEGIN

            ...
            IF photoid = -1 THEN
                newphotoval := NULL;
            ELSE
                newphotoval := photoid;
            END IF;

            UPDATE
                pages
            SET
                n_id_photo = newphotoval
            WHERE
                n_id = pageid;

            IF NOT FOUND THEN
                RAISE EXCEPTION ''Could not update photo : ref p2'';
                RETURN 0;
            END IF;
            ...

        RETURN 1;

    END;'
        LANGUAGE plpgsql;

---------------------------------------------------------------------

                                         Table "public.pages"
    Column     |            Type             |                        Modifiers
---------------+-----------------------------+---------------------------------------------------------
 n_id          | integer                     | not null default nextval('public.pages_n_id_seq'::text)
 dt_created    | timestamp without time zone | default ('now'::text)::timestamp(6) with time zone
 dt_modified   | timestamp without time zone | default ('now'::text)::timestamp(6) with time zone
 t_title       | character varying(100)      |
 t_text        | text                        |
 n_story       | integer                     | not null
 n_style       | smallint                    | default 1
 n_id_photo    | integer                     |
 n_id_sound    | integer                     |
 ti_length     | time without time zone      | default '00:00:06'
 n_page_number | smallint                    | default 1
 b_prototype   | boolean                     | default false
Indexes: pages_pkey primary key btree (n_id)
Foreign Key constraints: $1 FOREIGN KEY (n_story) REFERENCES stories(n_id) ON UPDATE CASCADE ON DELETE CASCADE,
                         $2 FOREIGN KEY (n_id_photo) REFERENCES photo(n_id) ON UPDATE CASCADE ON DELETE SET NULL,
                         $3 FOREIGN KEY (n_id_sound) REFERENCES sound(n_id) ON UPDATE CASCADE ON DELETE SET NULL
Triggers: tr_update_modified_time,
          tr_update_modified_time_sp

                                  Table "public.photo"
    Column     |     Type     |                        Modifiers
---------------+--------------+---------------------------------------------------------
 n_id          | integer      | not null default nextval('public.photo_n_id_seq'::text)
 n_width       | smallint     |
 n_height      | smallint     |
 c_orientation | character(1) |
 data          | bytea        |
 label         | character(2) |
Indexes: photo_pkey primary key btree (n_id)

Here is a row from pages with an invalide n_id_sound:

 n_id |         dt_created         |        dt_modified         | t_title | t_text  | n_story | n_style | n_id_photo |
n_id_sound| ti_length | n_page_number | b_prototype  

------+----------------------------+----------------------------+---------+---------+---------+---------+------------+------------+-----------+---------------+-------------
    6 | 2004-01-06 17:35:07.662787 | 2004-01-06 17:35:07.751908 | Rubbish | Rubbish |       3 |       1 |          1 |
   99999 | 00:00:06  |             1 | f 
(1 row)


--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>