[BUGS] BUG #4828: Fault a foreign key

[alex]

The following bug has been logged online:

Bug reference:      4828
Logged by:          alex
Email address:      alexafanas...@yandex.ru
PostgreSQL version: PostgreSQL8.3.7
Operating system:   Windows XP 2002 SP2
Description:        Fault a foreign key
Details: 

Run in psql:

--DROP DATABASE test_fk;
CREATE DATABASE test_fk WITH TEMPLATE = template0;

ALTER DATABASE test_fk OWNER TO postgres;
\connect test_fk


CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = public, pg_catalog;

CREATE TABLE tbl11 (
    id_tbl1 serial NOT NULL,
    cl1 integer NOT NULL,
    naim text
);

CREATE TABLE tbl12 (
    id_tbl2 serial NOT NULL,
    cl1 integer NOT NULL,
    kod character(1)
);

ALTER TABLE public.tbl12 OWNER TO postgres;

CREATE FUNCTION tr_f1() RETURNS trigger
    AS $$
BEGIN
IF NEW.kod = 'a' THEN
        NEW.cl1 = OLD.cl1;
END IF;
RETURN NEW;
END;
$$
    LANGUAGE plpgsql;

CREATE FUNCTION tr_f2() RETURNS trigger
    AS $$
BEGIN
IF OLD.kod = 'c' THEN
        RETURN NULL;    
END IF;
RETURN OLD;
END;
$$
    LANGUAGE plpgsql;

--

INSERT INTO tbl11(cl1,  naim) VALUES ( 1, '1/1');
INSERT INTO tbl11(cl1,  naim) VALUES ( 2, '1/2');

INSERT INTO tbl12( cl1,  kod)    VALUES (  1, 'a');
INSERT INTO tbl12( cl1,  kod)  VALUES (  1, 'b');
INSERT INTO tbl12( cl1,  kod)    VALUES ( 2, 'c');    
INSERT INTO tbl12( cl1,  kod)    VALUES ( 2, 'd'); 

ALTER TABLE ONLY tbl11  ADD CONSTRAINT sui UNIQUE (cl1);

CREATE TRIGGER tr1
    BEFORE UPDATE ON tbl12
    FOR EACH ROW
    EXECUTE PROCEDURE tr_f1();

CREATE TRIGGER tr2
    BEFORE DELETE ON tbl12
    FOR EACH ROW
    EXECUTE PROCEDURE tr_f2();

ALTER TABLE ONLY tbl12  ADD CONSTRAINT fk FOREIGN KEY (cl1) REFERENCES
tbl11(cl1) ON UPDATE CASCADE ON DELETE CASCADE;



Run in psql:
UPDATE tbl11   SET  cl1=5 WHERE cl1=1;
DELETE  FROM tbl11 WHERE  cl1=2;
SELECT * FROM tbl12 WHERE not exists(select 1 from  tbl11 WHERE tbl11.cl1 =
tbl12.cl1);

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs