Thread: delete cascade question

delete cascade question

From
Glenn MacGregor
Date:
Hi All,

I am using a cascade delete on the following table:

vid    vname        parentname
1     n1@test.com
2     n2@test.com         n1@test.com
3     n3@test.com         n1@test.com
4     n4@test.com         n1@test.com
5     n5@test.com         n3@test.com

So the delete cascade states if I am deleting a row whose vname matches
the parentname delete those rows and it works fine.

I just changed the values of parentname (but the vname values stay the same)

vid    vname        parentname
1     n1@test.com
2     n2@test.com         n1
3     n3@test.com         n1
4     n4@test.com         n1
5     n5@test.com         n3

Is there a way to do the same cascade delete with these values? I can
select the correct info from vname in a query:

select substring(vname, from 0 for position('@' in vname)) from table1;

This works, so I tried to put that in the cascade but it failed.

Is there any way to accomplish this?

    Thanks

        Glenn MacGregor



Re: delete cascade question

From
"Greg Sabino Mullane"
Date:
-----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-----



Re: delete cascade question

From
Glenn MacGregor
Date:
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)


Re: delete cascade question

From
Stephan Szabo
Date:
On Tue, 27 Apr 2004, Glenn MacGregor wrote:

> 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?

AFAIK only by writing your own trigger.  You could possibly get away with
using the foreign key trigger code as a starting point.