Skip site navigation (1) Skip section navigation (2)

Doubt on foreign key and deletions

From: Roberto Rezende de Assis <rezende_assis(at)yahoo(dot)com(dot)br>
To: pgsql-novice(at)postgresql(dot)org
Subject: Doubt on foreign key and deletions
Date: 2004-03-28 03:17:31
Message-ID: 406643CB.106@yahoo.com.br (view raw or flat)
Thread:
Lists: 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)
=================================================================================



Responses

pgsql-novice by date

Next:From: Stephan SzaboDate: 2004-03-28 06:16:34
Subject: Re: Doubt on foreign key and deletions
Previous:From: M. BastinDate: 2004-03-28 02:20:56
Subject: Re: Images in Database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group