[odb-users] Deleting members from child tables in a one-one relationship

[Szumowski, Thomas]

I had a question about the erase feature in ODB. Using the "relationships" example to start, I added a few lines after the first commit that erases the employer "Simple Tech Ltd". See the attached file (remove .allow and unpack).

I understand the relationship with John/Jane and Simple Tech as a one-to-one with employee referencing employer. In terms described at http://www.sqlite.org/foreignkeys.html, this makes the employee the "child table" and the employer the "parent table". So I am essentially deleting a member of the parent table. In the above link, there is much discussion on the various behaviors one can define upon an attempt to delete a member of the parent table (see section 4.3).

I have a few questions about ODB's expected behavior when deleting a member of a parent table (e.g. "employer").


1.       The SQL equivalent of the one-to-one relationship defined in the manual seems to match the relationship between tables TRACK and ARTIST in section 1 of the above link. In that section, a call of "DELETE FROM artist WHERE artistname = 'Frank Sinatra'" results in the error "SQL error: foreign key constraint failed".

When I run the attached code, it runs without error. Does ODB do handling in the background to manage potential errors like the one listed?


2.       The resulting database after running the attached code has John and Jane remaining, but Simple Tech Ltd deleted. In the employee table, Simple Tech is still listed as the employer. What would happen employer were to have more fields, and we later wished to load John. Here we now have an invalid foreign key since the employer was deleted. The link above has means of managing those events in section 4.3. For example, "REFERENCES XYZ ON DELETE SET NULL" Would set the employer field in John and Jane to NULL.

Is there a way to define this type of behavior in ODB?

-Tom
-------------- next part --------------
A non-text attachment was scrubbed...
Name: driver.zip.allow
Type: application/octet-stream
Size: 1388 bytes
Desc: driver.zip.allow
Url : http://codesynthesis.com/pipermail/odb-users/attachments/20110824/94a866a0/driver.zip.obj