Re: delete cascade question - Mailing list pgsql-admin

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)