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
**********************************
src.tar.gz
Description: GNU Zip compressed data
