From: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-admin@postgresql.org
Subject: Re: delete cascade question
Date: Tue, 27 Apr 2004 03:04:51 -0000
Message-ID: <4c1150790b46c0b4d7dead564c4b99aa@biglumber.com> (raw)
In-Reply-To: <408821D6.2000106@highstreetnetworks.com>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> I am using a cascade delete on the following table:
> ...
> So the delete cascade states if I am deleting a row whose vname
> matches the parentname delete those rows and it works fine.
> ...
> This works, so I tried to put that in the cascade but it failed.
Not sure what you mean by "put that in the cascade." You will have
to show us your actual tables and contraints. In general, "on delete cascade"
refers to what happens to foreign key rows when the referenced key is
deleted. Similarly, you can set a constraint as "on update cascade"
in which case the changes in the parent table are also made in the child
tables. Here is a quick example that may help:
CREATE TABLE "myparent" (
"a" INTEGER,
"b" TEXT unique
);
CREATE TABLE "mychild" (
"c" INTEGER,
"d" TEXT NOT NULL
);
ALTER TABLE "mychild" ADD CONSTRAINT "mychild_d_fk"
FOREIGN KEY (d) REFERENCES myparent(b)
ON DELETE CASCADE ON UPDATE CASCADE;
INSERT INTO myparent(a,b) VALUES (1,'Garibaldi');
INSERT INTO myparent(a,b) VALUES (2,'Zathras');
INSERT INTO myparent(a,b) VALUES (3,'G''Kar');
- -- This fails, because the foreign key constraint catches the typo:
INSERT INTO mychild(c,d) VALUES (1,'Garabaldi');
INSERT INTO mychild(c,d) VALUES (9,'Garibaldi');
INSERT INTO mychild(c,d) VALUES (10,'Zathras');
INSERT INTO mychild(c,d) VALUES (11,'Zathras');
SELECT * FROM mychild;
greg=# SELECT * FROM mychild;
c | d
- ----+-----------
9 | Garibaldi
10 | Zathras
11 | Zathras
UPDATE myparent SET b = 'Chief' WHERE b='Garibaldi';
- -- ON UPDATE CASCADE has changed the name in both tables:
SELECT * FROM mychild;
c | d
- ----+---------
10 | Zathras
11 | Zathras
9 | Chief
DELETE FROM myparent WHERE b = 'Zathras';
- -- ON DELETE CASCADE has removed all the Zathras's
SELECT * FROM mychild;
c | d
- ---+-------
9 | Chief
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200404262305
-----BEGIN PGP SIGNATURE-----
iD8DBQFAjc4QvJuQZxSWSsgRAtS8AKCjEkiZ5uFn6y88eqQX9/OoT/TbcACgs1qJ
FnC9Q9O0qkljz7sLTY7Czhw=
=RPTN
-----END PGP SIGNATURE-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: greg@turnstep.com
Subject: Re: delete cascade question
In-Reply-To: <4c1150790b46c0b4d7dead564c4b99aa@biglumber.com>
References: <408821D6.2000106@highstreetnetworks.com> <4c1150790b46c0b4d7dead564c4b99aa@biglumber.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox