Hi Craig, Joerg,
I agree that FK constraints are an important and powerful feature of
relational databases. They provide for referential integrity, and, as a
side effect, prevent people/applications from deleting rows unless FKs
are nullified first.
My impression is that below you are talking of different delete
semantics in the object domain:
1) Deleting an object means that all references to that object are
nullify implicitly.
2) Deleting an object is only successful if there are no references to
that object.
I think it would make sense to support both semantics in JDO.
Regards,
Michael
Jörg von Frantzius wrote:
Hi Craig,
Craig L Russell schrieb:
Hi Jörg,
On Jul 17, 2006, at 10:27 AM, Jörg von Frantzius wrote:
Craig L Russell schrieb:
If that's the case, then I wonder why a user would want to have FK
constraints in his schema at all? They wouldn't be of much use then.
If only using JDO, I agree. What matters is the consistency of the
object model, which is managed by the JDO implementation without
help from the database. But seldom is JDO the only access path to
the database.
To me this means losing an important data security feature when using
JDO over other access paths to the DB. FK constraints can prevent you
from inadvertently deleting data, and by silently removing any
dangling references on the object level (and so circumventing any FK
constraints) you are entirely losing this kind of "safety belt"! JDO
would never tell you about these dangling references, but instead
would silently go and delete that information.
I'm not sure I understand your concern. Is it ok for the application
to explicitly nullify references to instances that it deletes? It's
not clear to me how a nullable database constraint improves the
reliability of your user's code.
Yes exactly, I find it good if the application programmer must
explicitly nullify any references before deleting. That way he'll know
what relationships exactly he is going to remove the object from. If it
happens transparently and without notice, he might not realize the
consequences. I find it adequate that code for deleting objects must be
written with care.
So in my opinion, and from my experience, FK constraints help to prevent
programming mistakes from creating serious data loss.
On the other hand, if it is a requirement of the object model that
there must be a non-null reference to an instance, then would a
not-null foreign key satisfy the requirement? In this case, JPOX's
automatic nullification of the reference would cause a (null)
constraint violation. Or JPOX would throw an exception if it knew
about the not-null constraint. Or defer the delete until commit, by
which time the other side would have to have been set to a valid
not-deleted instance. And the application would have to be careful to
set the other side of the relationship.
That's interesting, but I wasn't thinking about FKs with an additional
NOT-NULL constraint here. I was thinking of simply not allowing the
deletion to happen at all, as it turned out to our own surprise, please
see below.
As you know, there are no TCK tests for these cases. I'd like to
formalize the specification and add some tests once we all agree on
the semantics.
As I wrote in an earlier mail, we had a case here where we were
really lucky that the FK constraint would fire upon deletion, because
we didn't think of all the relationships that an object would be
removed from, and for which we really didn't want to lose the
information. That was for users that are logged as creators and
modifiers of other objects: if you can delete a user, you'll lose the
information about who created or edited an object, something that
would have gone lost silently forever if it wasn't for the FK
constraints firing.
So is it the case that explicitly nullifying the relationship is
illegal for the user? I'm trying to picture the security domain model
where a user is authorized to delete from a table but is not
authorized to set the column of a related table to null. How is this
supposed to work?
What I meant to say is that the "delete user" functionality of the
application turned out to not having been thought out well enough. It
was only when an end-user clicked the "delete"-button on another user
that had already created or modified other objects, that we realized we
don't want to allow a deletion in this case at all. And we realized it
only because luckily an SQLException was thrown due to the constraint
violation.
In this case, it would have been a serious mistake if the programmer had
nullified the relationships explicitly, but in doing so he is forced to
think twice about it, hopefully, and you can't prevent people from
creating damage on purpose ;)
I may also add that, as far as I know, JPOX nulls out only for
one-one bidirectional, not for one-many, so there would be some
inconsistency of behaviour here.
Interesting point, but without another level of detail, it's hard to
evaluate this statement.
If you look at the code in DeleteRequest, you'll see that in
execute() it'll null out FKs for any fields obained using
getFildsWithRelatedObjects(). The latter's javadoc says
/**
* All fields that have related objects and where we use the
related object to
* maintain a relationship. These are used where we have 1-1
bidirectional
* relationships with a single FK (in the other object).
* @return The fields with related objects
*/
public FieldMetaData[] getFieldsWithRelatedObjects()
That's the case for 1-1 relationships with "mapped-by" on the side
where the object is deleted. From what I saw in my tests, that's
where the nulling out happens, and it doesn't happen for one-many
relationships with "mapped-by", so you'll see FK constraints
violations there.
For one-many relationships there is no additional foreign key. Take
the case of deleting an Employee for which there is a
Department-Employee relationship. There is no explicit database action
needed when deleting the Employee because there is no explicit
reference from the DEPARTMENT table to the key values in the EMPLOYEE
table. The primary key in EMPLOYEE defines the relationship.
What I was thinking of would in this example be a deletion of a
Department that has Employees. I had understood you that you want to
transparently have the Employees be removed from the Department when it
is deleted. Currently JPOX doesn't do that, and an FK violation occurs.
Let's take this a step further. Let's assume that Department has an
"employees" Collection field annotated with "mapped-by". That means we
have two Java object references that are represented by one FK on the
database level. From an object-oriented perspective, I shouldn't need to
know that there is only a single FK, and where it is exactly. However, I
can delete an Employee without further action, while I cannot delete a
Department without further action. There is an asymmetry here that is
rooted in the relational nature of the database, and, for the sake of
transparency, it should be hidden. For the sake of data security, I'd
prefer to hide the asymmetry by throwing exceptions in both cases
So I'd even say that it should not be possible to delete an Employee
before removing it from its Department's employees collection, because
there won't be any FK violation notifying of the dangling object
reference. However, if you find that a little over the top, I won't
insist too long as our code generator already causes exceptions to be
thrown in these cases...
Regards,
Craig
Regards,
Craig
Regards,
Jörg
Craig L Russell schrieb:
2. Deletion of objects when foreign-key is present (JDO-392) (any
and all) It seems that there are two different issues: managing
the memory model and managing the database. Craig opines that the
general case of consistency is already covered in the chapter on
mapping, requiring that the object model be consistent after a
flush. Object model consistency would disallow a reference to a
deleted object, so the natural behavior would be to nullify the
reference to the deleted object. For to-many relationships mapped
to a foreign key on the other side, the consistency rule would
delete the reference from the collection on the one- side of the
relationship. So it seems that the JPOX behavior as originally
reported is consistent and we might simply document it in the
specification.
Why does object model consistency disallow a reference to a deleted
object?
<joerg.von.frantzius.vcf>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!
<joerg.von.frantzius.vcf>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!
--
-------------------------------------------------------------------
Michael Watzek [EMAIL PROTECTED] Engineering GmbH
mailto:[EMAIL PROTECTED] Buelowstr. 66
Tel.: ++49/30/235 520 36 10783 Berlin - Germany
Fax.: ++49/30/217 520 12 http://www.spree.de/
-------------------------------------------------------------------
