strange behavior using foreign keys - Mailing list pgsql-general

Hello PG's,

I noticed a strange behavior using foreign keys in postgresql 7.0.3 when I
want to delete a row. May be you can enlight me! :)

I created three tables with the following referential relations:

create table MASTER (ID integer not null,
                     NAME varchar(30) not null,
                     primary key (ID));

create table REFER1 (ID integer not null,
                     TEAM integer not null,
                     NAME varchar(30),
                     primary key (ID, TEAM),
                     foreign key (ID) references MASTER (ID));

create table REFER2 (ID integer not null,
                     TEAMA integer not null,
                     TEAMB integer not null,
                     RESULTA integer,
                     RESULTB integer,
                     primary key (ID, TEAMA, TEAMB),
                     foreign key (ID) references MASTER (ID),
                     foreign key (TEAMA) references REFER1 (TEAM),
                     foreign key (TEAMB) references REFER1 (TEAM));

As you can see REFER2 refers to both tables MASTER and REFER1 using
one field twice (TEAM). REFER1 refers to a field in table MASTER.

Now I want to insert some data:

insert into MASTER (ID, NAME) values (0,'test_string_master');
insert into MASTER (ID, NAME) values (2,'test_string_master');

insert into REFER1 (ID, TEAM, NAME) values (0,1,'test_string_refer1');
insert into REFER1 (ID, TEAM, NAME) values (2,1,'test_string_refer1');

insert into REFER2 (ID, TEAMA, TEAMB, RESULTA, RESULTB) values (0,1,1,0,0);
insert into REFER2 (ID, TEAMA, TEAMB, RESULTA, RESULTB) values (2,1,1,0,0);

That's all! After inserting the data the tables are filled as follows:

jose=> select * from MASTER;
 id |        name
----+--------------------
  0 | test_string_master
  2 | test_string_master
(2 rows)

jose=> select * from REFER1;
 id | team |        name
----+------+--------------------
  0 |    1 | test_string_refer1
  2 |    1 | test_string_refer1
(2 rows)

jose=> select * from REFER2;
 id | teama | teamb | resulta | resultb
----+-------+-------+---------+---------
  0 |     1 |     1 |       0 |       0
  2 |     1 |     1 |       0 |       0
(2 rows)

And now I want to delete a complete ID from the database:

jose=> delete from REFER2 where id = 2;
DELETE 1

jose=> select * from REFER2;
 id | teama | teamb | resulta | resultb
----+-------+-------+---------+---------
  0 |     1 |     1 |       0 |       0
(1 row)

jose=> delete from REFER1 where ID = 2;
ERROR:  <unnamed> referential integrity violation - key in refer1 still
referenced from refer2

But there is no row in REFER2 referencing a key in table REFER1.

What is wrong???

Best Regards / Mit freundlichen Gruessen / Un saludo

Jose Manuel Lorenzo Lopez