Re: strange behavior using foreign keys - Mailing list pgsql-general

On Sun, 24 Jun 2001, Jose Manuel Lorenzo Lopez wrote:

> 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.
>

Yes there is. You have only deleted the one of the two rows from REFER2. There is still one remaining row (0,1,1,0,0)
whichindeed references team '1' from REFER1. When you created the tables you didn't define how to handle the
referencingthrough the foreign key in case of DELETE/UPDATE. Postgres's default is NO ACTION. This means that if you
tryto delete or update a row that is referenced from a row in another table, then the delete or update won't take
place.If on the other had you had given ON DELETE CASCADE, the you could do the delete on REFER1 and have the
corresponding/referencingrows from REFER2 deleted as well. 

Check the manual of CREATE TABLE to find more about the FOREIGN KEY clause.


cheers,
thalis

> What is wrong???
>
> Best Regards / Mit freundlichen Gruessen / Un saludo
>
> Jose Manuel Lorenzo Lopez
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>