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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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