I do not know if this is a bug, but for me this is a strange behavior.
I will describe the following process.
CREATE TABLE "test1" (
"id" SERIAL,
"sum" numeric ,
"name" char (20) ,
PRIMARY KEY ("id"), UNIQUE ("id"));
CREATE TABLE "test2" (
"id1" SERIAL,
"sum1" numeric ,
"name2" char (20) ,
PRIMARY KEY ("id"), UNIQUE ("id"));
ALTER TABLE "test2" ADD "id" SERIAL ;
ALTER TABLE test2 ADD CONSTRAINT fk_id FOREIGN KEY ("id") REFERENCES
"test1"("id") ON DELETE CASCADE;
then
CREATE RULE insert_test2 AS ON INSERT TO test1 DO INSERT INTO test2
(sum1, name2, id, id1) VALUES (new.sum, new.name, new.id,
nextval('"test2_id1_seq"'::text));
trying to insert into table1 there is a referencial integrity error -
Why?.
OK I say - the values whitin transaction are unknown.
defening the key as DEFEREBLE is not solution too, since the values
are check at the end of the transaction.
OK - I say - then I must delete the refencial integrity and try again.
On my system this say (using pgadmin)
DROP TRIGGER "RI_ConstraintTrigger_64226" ON "test2";
DROP TRIGGER "RI_ConstraintTrigger_64228" ON "test1";
DROP TRIGGER "RI_ConstraintTrigger_64230" ON "test1";
After that I try to insert into table1:
INSERT INTO "test1" ("id", "sum", "name") VALUES
(NEXTVAL('"test1_id_seq"'::text), '1', '2');
then;
select * from table1;
id sum name
4 1.000000 2
select * from table2;
id1 sum1 name2 id
2 1.000000 2 3
as you see the id in table1 is 4, but the id in table2 is 3
Regards
Tony Tomov
