X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C3C5AD1B4D7 for ; Tue, 27 Apr 2004 00:04:55 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 72103-04 for ; Tue, 27 Apr 2004 00:04:53 -0300 (ADT) Received: from biglumber.com (biglumber.com [207.228.252.42]) by svr1.postgresql.org (Postfix) with SMTP id B7ED0D1B4A3 for ; Tue, 27 Apr 2004 00:04:52 -0300 (ADT) Received: (qmail 30995 invoked from network); 27 Apr 2004 03:04:52 -0000 Received: from unknown (HELO localhost) (207.228.252.42) by 0 with SMTP; 27 Apr 2004 03:04:52 -0000 From: "Greg Sabino Mullane" To: pgsql-admin@postgresql.org Subject: Re: delete cascade question X-PGP-Key: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8 X-Request-PGP: http://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 In-Reply-To: <408821D6.2000106@highstreetnetworks.com> Date: Tue, 27 Apr 2004 03:04:51 -0000 X-Mailer: JoyMail 1.47 Message-ID: <4c1150790b46c0b4d7dead564c4b99aa@biglumber.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200404/316 X-Sequence-Number: 13289 -----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-----