Re: [Hsqldb-developers] ON [DELETE|UPDATE] SET [NULL|DEFAULT]

[<hsq...@li...>]

Thanks Sebastian,

I will apply these patches to the CVS soon.

I think we can avoid infinite recursion in cascading deletes fairly easily.
Regarding other actions I must see what can be done. In general, if we can
use the graph of references as opposed to the actual rows, the solution
would be more universal and have no need for marking individual rows.

Regards

Fred

----- Original Message -----
From: <hsq...@li...>
To: "HSQLDB Developers" <hsq...@li...>
Sent: 11 November 2002 12:37
Subject: [Hsqldb-developers] ON [DELETE|UPDATE] SET [NULL|DEFAULT]


  Hi ....

  Did some more work on the
  'ON [DELETE|UPDATE} SET [NULL|DEFAULT]' stuff.

  1)  processCreateTable and  processCreateFk now understand
      the SET [DEFAULT|NULL] option.

  2)  procressCreateTable throws
      a COLUMN_TYPE_MISMATCH exception if someone tries
      to specify an 'SET DEFAULT' for a column without an
      explicit default value

  3)  checkCascadeUpdate has been extendet to set the
      FK values to NULL or the default value if the
      contraint says so.

  4)  checkCascadeDelete merely switches over to
      checkCascadeUpdate when the constraint is a
      'SET [NULL|DEFAULT]' constraint.


  There are however a bunch of issues with the whole
  ON [UPDATE|DELETE] scheme which I believe are not easily
  solved without mayor redesigns. Most of the stuff is
  related to self referential foreign keys and the fact
  that updates are actually sequences of delete/insert
  actions.

  1) Deleting/Updating self referential records results
     in infinite recursion. i.e:

     If I have a table like

     create table a(a int primary key,
                    b int,
                    foreign key(b)
                     references a(a)
                      on update cascade
                      on delete cascade)

    And If I inserted a tuple like (1,1)
    into the table (by turning of referentiual integrity checks)
    i end up in endless recursion when trying to delete/modify
    the record. This was already true befor I've added my stuff.

    I actually use records like these in my application, where I
    build up a tree structure with self referential foreign keys
    The root node in the tree is defined as a record refering to
    itself.

  2) If I have a table like

      create table a(a int primary key,
                    b int default 99,
                    foreign key(b)
                     references a(a)
                      on update set default
                      on delete set default)

     and the tuples are (once again build up by turning of
     referential integrity for a while)

     (1,1)
     (2,1)
     (3,1)

     I may try to do an update like:

     UPDATE A set A=33 where A=3

     This is forbidden because an 'SET DEFAULT' to the value 99
     is not permitted since there is not value a=99 allowing
     the update of b to the default value 99.

     If however the tuples are.

     (1,1)
     (2,1)
     (3,1)
     (99,1)

     It would be fine to do the

     UPDATE A set A=33 where A=3

     The result would be

     (1,1)
     (2,1)
     (3,99)
     (99,1)

     BUT

     Now its perefrctly fine to do an

     DELETE FROM A where A=99

     ceckUpdateDelete checks if there are
     records refering to the (99,1) tuple and
     finds (3,99). The contraint dictates that
     b whould be set to 99. It therefor checks
     if this is permitted and finds the tuple (99,1).

    3) The only way of solving most of these issues
       within the current design would be some
       'will be deleted' flag we might add to the tuples
       while checking referebtial integrity.


   Cheers

   Sebastian


--
**********************************
Dr. Sebastian Kloska
Head of Bioinformatics
Scienion AG
Volmerstr. 7a
12489 Berlin
phone: +49-(30)-6392-1708
fax:   +49-(30)-6392-1701
http://www.scienion.de
**********************************