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

[Boris Kolpackov]

Hi Thomas,

Szumowski, Thomas <thomas.szumowski at lmco.com> writes:

> 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?

Up to and including version 1.5.0, ODB did not enable foreign key 
constraint checking in SQLite (SQLite keeps it disabled by default).
So in these versions such constraint violations were simply ignored.

For the upcoming 1.6.0 release (and in the 1.6.0.a1 pre-release that
I mentioned to Eric), we now by default enable foreign keys in SQLite.
We also make foreign keys corresponding to object relationships 
deferred (INITIALLY DEFERRED; see Section 4.2 in the SQLite foreign
keys page) which means they are checked at the end of the transaction.
So with version 1.6.0.a1 and later, in this situation you would get a
"foreign key constraint failed" error on commit.


> 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?

I see how ON DELETE SET NULL or ON DELETE CASCADE can be useful. However,
there is currently no way to specify this in the ODB pragma language.
The only way would be to either modify the generated schema or use
something like ALTER TABLE to add the ON DELETE clause.

There are also potential issues with using ON DELETE. If you had one
of the employers corresponding to the deleted Simple Tech Ltd loaded
into the application's memory, there would be no way to synchronize
its state with the ON DELETE mechanism. If you were to later call
update() with such an object, you would effectively restore an
invalid key. So, in this light, perhaps making sure that object
references are consistent (e.g., first deleting all the employees
before deleting the employer) and getting an error if they aren't
is the best approach.

Boris