Re: delete cascade question

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(at)test(dot)com | cell | f | cell(at)test(dot)com | cell | test.xml |
192.168.0.23 | | 1 | t | group1

2 | test(at)test(dot)com_if_1 | cell | f | cell(at)test(dot)com | cell | test_if.xml |
192.168.0.23 | test(at)test(dot)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(at)test(dot)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(at)test(dot)com | cell | f | cell(at)test(dot)com | cell | test.xml |
192.168.0.23 | | 1 | t | group1

2 | test(at)test(dot)com_if_1 | cell | f | cell(at)test(dot)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(at)turnstep(dot)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(at)postgresql(dot)org)