Skip site navigation (1) Skip section navigation (2)

BUG #4828: Fault a foreign key

From: "alex" <alexafanasjev(at)yandex(dot)ru>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4828: Fault a foreign key
Date: 2009-05-28 19:32:52
Message-ID: 200905281932.n4SJWq0G036345@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      4828
Logged by:          alex
Email address:      alexafanasjev(at)yandex(dot)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);

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2009-05-28 19:41:47
Subject: Re: BUG #4828: Fault a foreign key
Previous:From: Peter KoczanDate: 2009-05-28 19:09:42
Subject: Re: BUG #4824: KRB5/GSSAPI authentication fails when user != principal

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group