Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    98

    cascading delete and primary key

    i have 2 tables set up:

    tbl_a
    aid primary key

    tbl_b
    aid primary key
    foreign key (aid)
    references tbl_a
    on delete cascade


    however, when i try to delete a record in tbl_a, it gives me a violation of primary key error. i had to delete tbl_b's record first, then delete the record in tbl_a.

    i seem to remember if my tbl_b has a slightly different schema:

    tbl_b
    bid primary key
    aid
    foreign key (aid)
    references tbl_a
    on delete cascade

    the cascading delete would work without a hitch.
    is there something i am missing?

    mark

  2. #2
    Join Date
    Nov 2002
    Posts
    98

    Re: cascading delete and primary key

    seems the problem is a little more complicated than i thought

    it seems the problem comes from more than one tables having foreign keys to the same parent table.

    when i tried to delete a record in the parent table, it gives a primary key error saying the key is still being referenced, but ON DELETE CASCADE are enabled on all the child table.

    did i mistaken the meaning of ON DELETE CASCADE, or this has to be solved via trigger?

  3. #3
    Join Date
    Nov 2002
    Posts
    98
    some more info to the problem:

    structures for some of the tables are similar to the diagrams below:

    A<-B<-C
    ^ ^-D<-E
    ^---F<-G

    say i want to delete a record in A (on delete cascade are enabled in all columns involving in the foreign keys relationships) logically any records referencing the foreign key from A should all be deleted, but informix gives me "the primary key is still being referenced to" error message. same thing would happen if i tried to delete a record in B, etc...

    Am I missing something, or this is a situation that needs to be done via trigger?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    This must be an Informix-specific problem. Your examples should all work, and do work in Oracle (I have just tried).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •