Re: Doubt on foreign key and deletions

On Sun, 28 Mar 2004, Roberto Rezende de Assis wrote:

> Hi  I'm trying this script to check if I can delete a row in a table A
> and make the table B which is referencing the A's row, instead of delete
> it's own row put it back to it's default value, by  the documentation on
> CRATE TABLE of the Postgresql 7.4.2  it appears to me that my script was ok.

If you're wondering about the error, SET DEFAULT still means that the
referenced value must exist, so...

> ==============================================================================
> CREATE TABLE tb_1(
>     cod_1    integer        NOT NULL DEFAULT 10,
>     nome_1    char(2)        NOT NUll,
>         CONSTRAINT id_tb_1 PRIMARY KEY(cod_1)
> );
> CREATE TABLE tb_2(
>     cod_2    integer        NOT NULL DEFAULT 20,
>     nome_2    char(2)        NOT NUll,
>         CONSTRAINT id_tb_2 PRIMARY KEY(cod_2)
> );
> CREATE TABLE rel_1(
>     cod_rel    integer        NOT NULL DEFAULT 1,
>     cod_1    integer        NOT NULL DEFAULT 30,
>     cod_2    integer        NOT NULL DEFAULT 40,
>     nome_3    char(2)        NOT NULL,
>         CONSTRAINT id_rel_1 PRIMARY KEY(cod_rel,cod_1,cod_2),
>         CONSTRAINT fk_cod_1 FOREIGN KEY(cod_1)
>             REFERENCES tb_1(cod_1)
>                 ON DELETE SET DEFAULT
>                 ON UPDATE CASCADE
>                DEFERRABLE,
>         CONSTRAINT fk_cod_2 FOREIGN KEY(cod_2)
>             REFERENCES tb_2(cod_2)
>                 ON DELETE SET DEFAULT
>                 ON UPDATE CASCADE
>                 DEFERRABLE
> );
> INSERT INTO tb_1(cod_1,nome_1) VALUES (10,'aa');
> INSERT INTO tb_1(cod_1,nome_1) VALUES (11,'ab');
> INSERT INTO tb_1(cod_1,nome_1) VALUES (12,'ac');
> INSERT INTO tb_2(cod_2,nome_2) VALUES (20,'za');
> INSERT INTO tb_2(cod_2,nome_2) VALUES (21,'zb');
> INSERT INTO tb_2(cod_2,nome_2) VALUES (22,'zc');
> INSERT INTO rel_1(cod_rel,cod_1,cod_2,nome_3) VALUES (1,10,20,'zz');
> INSERT INTO rel_1(cod_rel,cod_1,cod_2,nome_3) VALUES (2,10,20,'vv');
> INSERT INTO rel_1(cod_rel,cod_1,cod_2,nome_3) VALUES (3,12,21,'xx');
> INSERT INTO rel_1(cod_rel,cod_1,cod_2,nome_3) VALUES (4,11,22,'yy');
> SELECT * FROM tb_1;
> SELECT * FROM tb_2;
> SELECT * FROM rel_1;
> UPDATE tb_1 SET cod_1 = 13 WHERE cod_1 = 10;
> SELECT * FROM tb_1;
> SELECT * FROM rel_1;
> DELETE FROM tb_2 where cod_2 = 20;

This attempts to set cod_2 in the rel_1 rows referencing the deleted tb_2
row to 40 (the default for cod_2 on rel_1.) This fails because there's no
row in tb_2 with cod_2 value 40. If you add a tb_2 row with cod_2 of 40, I
get the two referencing rows getting 40 as their value.