Doubt on foreign key and deletions

Поиск
Список
Период
Сортировка
От Roberto Rezende de Assis
Тема Doubt on foreign key and deletions
Дата
Msg-id 406643CB.106@yahoo.com.br
обсуждение исходный текст
Ответы Re: Doubt on foreign key and deletions  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Список pgsql-novice
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.
But....
==============================================================================
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;
SELECT * FROM tb_2;
SELECT * FROM rel_1;
========================================================================
Then I get this output:
========================================================================
psql:bob.sql:5: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit
index 'id_tb_1' for table 'tb_1'
CREATE TABLE
psql:bob.sql:10: NOTICE:  CREATE TABLE / PRIMARY KEY will create
implicit index 'id_tb_2' for table 'tb_2'
CREATE TABLE
psql:bob.sql:27: NOTICE:  CREATE TABLE / PRIMARY KEY will create
implicit index 'id_rel_1' for table 'rel_1'
psql:bob.sql:27: NOTICE:  CREATE TABLE will create implicit trigger(s)
for FOREIGN KEY check(s)
CREATE TABLE
INSERT 27060 1
INSERT 27061 1
INSERT 27062 1
INSERT 27063 1
INSERT 27064 1
INSERT 27065 1
INSERT 27066 1
INSERT 27067 1
INSERT 27068 1
INSERT 27069 1
 cod_1 | nome_1
-------+--------
    10 | aa
    11 | ab
    12 | ac
(3 rows)

 cod_2 | nome_2
-------+--------
    20 | za
    21 | zb
    22 | zc
(3 rows)

 cod_rel | cod_1 | cod_2 | nome_3
---------+-------+-------+--------
       1 |    10 |    20 | zz
       2 |    10 |    20 | vv
       3 |    12 |    21 | xx
       4 |    11 |    22 | yy
(4 rows)

UPDATE 1
 cod_1 | nome_1
-------+--------
    11 | ab
    12 | ac
    13 | aa
(3 rows)

 cod_rel | cod_1 | cod_2 | nome_3
---------+-------+-------+--------
       3 |    12 |    21 | xx
       4 |    11 |    22 | yy
       1 |    13 |    20 | zz
       2 |    13 |    20 | vv
(4 rows)

psql:bob.sql:44: ERROR:  fk_cod_2 referential integrity violation - key
referenced from rel_1 not found in tb_2
 cod_2 | nome_2
-------+--------
    20 | za
    21 | zb
    22 | zc
(3 rows)

 cod_rel | cod_1 | cod_2 | nome_3
---------+-------+-------+--------
       3 |    12 |    21 | xx
       4 |    11 |    22 | yy
       1 |    13 |    20 | zz
       2 |    13 |    20 | vv
(4 rows)
=================================================================================



В списке pgsql-novice по дате отправления:

Предыдущее
От: "M. Bastin"
Дата:
Сообщение: Re: Images in Database
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: Doubt on foreign key and deletions