pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Glenn MacGregor <gtm@highstreetnetworks.com>
To: Greg Sabino Mullane <greg@turnstep.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: delete cascade question
Date: Tue, 27 Apr 2004 08:50:49 -0400
Message-ID: <408E5729.7080201@highstreetnetworks.com> (raw)
In-Reply-To: <4c1150790b46c0b4d7dead564c4b99aa@biglumber.com>
References: <4c1150790b46c0b4d7dead564c4b99aa@biglumber.com>

Sorry for being too vague. Here is my exact situation.

I have the following table currently:


                                            Table "xpressvim"
        Column        |          Type          | 
Modifiers
---------------------+------------------------+---------------------------------------------------------
  vimid               | integer                | not null default 
nextval('"xpressvim_vimid_seq"'::text)
  vimname             | character varying(128) | not null
  vimclass            | character varying(50)  | not null
  isadmin             | boolean                | not null default 'f'
  adminname           | character varying(136) |
  vimtype             | character varying(64)  |
  deviceconfig        | character varying(64)  |
  deviceip            | inet                   |
  parentname          | character varying(128) |
  discovereditemindex | bigint                 |
  islicensed          | boolean                |
  addevgroupname      | character varying(64)  |
Primary key: xpressvim_pkey
Unique keys: xpressvim_vimname_key
Triggers: RI_ConstraintTrigger_16786,
           RI_ConstraintTrigger_16788,
           RI_ConstraintTrigger_16792,
           RI_ConstraintTrigger_16794,
           RI_ConstraintTrigger_16802,
           RI_ConstraintTrigger_16804,
           RI_ConstraintTrigger_16846

The constraint that I am concerned with here is the following:

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "xpressvim"  FROM 
"xpressvim" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE 
PROCEDURE "RI_FKey_cascade_del" ('<unnamed>', 'xpressvim', 'xpressvim', 
'UNSPECIFIED', 'parentname', 'vimname');

Example data:
vimid | vimname | vimclass | isadmin | adminname | vimtype | 
deviceconfig | deviceip | parentname | discovereditemindex | islicensed 
| addevgroupname

1 | test@test.com | cell | f | cell@test.com | cell | test.xml | 
192.168.0.23 | | 1 | t | group1

2 | test@test.com_if_1 | cell | f | cell@test.com | cell | test_if.xml | 
192.168.0.23 | test@test.com | 2 | t | group1

So the above constraint says if I delete row 1 (vimid = 1) then row 2 
(vimid = 2) should be deleted as well because I an deleting the parent 
of 2. This works fine.

Now I need to change the parentname field (in the case of vimid = 2) 
from test@test.com to test and keep the vimname field as is. When I do 
this the constraint no longer works (which makes perfect sense).

1 | test@test.com | cell | f | cell@test.com | cell | test.xml | 
192.168.0.23 | | 1 | t | group1

2 | test@test.com_if_1 | cell | f | cell@test.com | cell | test_if.xml | 
192.168.0.23 | test | 2 | t | group1

delete row 1 and row 2 stays around.

Is there a way to create a constraint that will delete row 2 if I delete 
row 1 using a substring of the vimname to match with the parentname?

	Thanks

		Glenn






Greg Sabino Mullane wrote:
> -----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-----
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)




view thread (4+ messages)  latest in thread

Message-ID: <408E5729.7080201@highstreetnetworks.com>
Permalink:  ../408E5729.7080201@highstreetnetworks.com/
Also on:    postgresql.org/message-id/408E5729.7080201@highstreetnetworks.com

 · 

reply

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: gtm@highstreetnetworks.com, greg@turnstep.com
  Subject: Re: delete cascade question
  In-Reply-To: <408E5729.7080201@highstreetnetworks.com>
  References: <4c1150790b46c0b4d7dead564c4b99aa@biglumber.com> <408E5729.7080201@highstreetnetworks.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