That’s a great point Samuel :-)
AARON ROSENZWEIG / Chat 'n Bike <http://www.chatnbike.com/>
e: aa...@chatnbike.com <mailto:aa...@chatnbike.com> t: (301) 956-2319
> On Jul 28, 2019, at 8:08 PM, Samuel Pelletier <sam...@samkar.com> wrote:
>
> Hi Aaron,
>
> The nullify of the FK when a parent is deleted can be handled with Foreign
> Key Constraint in the database if the relationship is not an attribute.
>
> This works if you use a "real" engine that support deferred constraint
> checking like Oracle, Sql Server, FrontBase or PostgreSql at least. This is
> not supported in MySQL.
>
> ALTER TABLE "Employee" ADD CONSTRAINT
> "FOREIGN_KEY_Employee_CompanyID_Company_id" FOREIGN KEY ("CompanyID")
> REFERENCES "Company" ("ID") on delete set null DEFERRABLE INITIALLY DEFERRED;
>
> You have basically the same option as in EOF, you can have the DB react to
> delete of parent with these options:
> - deny (default behaviour)
> - set null (set the FK to null)
> - cascade (cascade te delete to children)
>
> The same options re allowed for update with the "on update xxx"
>
> Regards,
>
> Samuel
>
>> Le 28 juill. 2019 à 18:08, Aaron Rosenzweig via Webobjects-dev
>> <webobjects-dev@lists.apple.com <mailto:webobjects-dev@lists.apple.com>> a
>> écrit :
>>
>> Hi Robert,
>>
>> Alright it’s coming together but let’s make it concrete. Let’s make a
>> complete story shall we?
>>
>> “Employee” table has an FK to “Company”
>>
>> “Company” has a conceptual “toMany” to “Employee.” You could model it, or
>> not. If you model it, you could make it visible, or not (class property).
>> This is the big part of the question, how to model this “convenience”
>> relationship since it isn’t real, it raises questions.
>>
>> Given the above, we now delete a company object, what should happen?
>>
>> If you model the “Company.employees” to-many relationship and make it a
>> class property you have a choice for the delete rule:
>>
>> 1) Deny - if it finds at least one employee, it refuses the delete of the
>> company
>>
>> 2) Nullify - it goes out to find all the 5,000 employees and suddenly breaks
>> their bond the company so that they are now without a job.
>>
>> 3) Cascade - it goes out and terminates, lethally, all 5,000 employees
>> before destroying the company.
>>
>> I’m willing to bet, dollars to donuts, that 1/2/3 will be ignored if the
>> “employees” to-many relationship is not a class property. It’s gotta be
>> visible for it to do either of those things. That makes sense right? If the
>> idea of making it invisible is to not take the hit for faulting in 5k
>> employee objects, how could it possibly “nullify” (for example) without
>> faulting them in? That’s why it would HAVE to be a class property if you
>> want it to do that bookkeeping.
>>
>> Generally, you’d never delete a company unless you manually, through a
>> clever UI, allowed the user to re-home all the employees. In this story
>> line, I would not model the “Company.employees” to-many relationship at all.
>> If I ever needed that info, I would fetch “Employee.fetch(ec,
>> Employee.COMPANY.is
>> <https://ving.apple.com/proxy?t2=dE3O0r2E9w&o=http%3A%2F%2FEmployee.COMPANY.is>(appleComputer).”
>> That way I’m taking the hit only when it’s needed. I would also make a true
>> FK constraint in the DB that would prevent Apple from being deleted so long
>> as there was at least one employee.
>>
>> I realize your case is not Employee and Company… but any story that has so
>> many objects that you feel bad about modeling the to-many I’d feel the same
>> about. I wouldn’t want the deletion of the Company to automatically nullify
>> the 5k places. That said, it appears you need this… and for that the best
>> course of action would be either:
>>
>> A) Manually fetch the Employee’s where their company relationship is equal
>> to the one you are about to delete. Nullify all their relationships to
>> company. Delete the company. Save changes. This will take a while if there
>> is a plethora of employees. Might need a long running task so that the app
>> doesn’t timeout or block other users.
>>
>> B) Let SQL nullify the FK and then delete the company. This would be fast
>> and use little java memory. There are various helper methods to achieve this
>> but here is one: ERXEOAccesUtilities.updateRowsDescribedByQualifier()
>>
>> A and B could be encapsulated in a method
>> “Company.takeCareOfDependentsThenDelete()” that you create on Company.
>>
>>
>> AARON ROSENZWEIG / Chat 'n Bike
>> <https://ving.apple.com/proxy?t2=dE0W8n0x5N&o=http%3A%2F%2Fwww.chatnbike.com>
>> e: aa...@chatnbike.com <mailto:aa...@chatnbike.com> t: (301) 956-2319
>>
>>
>>
>>> On Jul 28, 2019, at 4:42 PM, Robert Hanviriyapunt <robertha...@gmail.com
>>> <mailto:robertha...@gmail.com>> wrote:
>>>
>>> Ok the root problem is that deleting records is leaving bad foreign keys.
>>>
>>> The reason for the problem is that I made a decision long ago that in
>>> certain circumstances I would model to-one relationships with a “hidden”
>>> to-many reverse relationship, hopefully to help save memory or something.
>>> The “hiding” is done by turning off the “class property” on the reverse
>>> to-many relationship but keep the nullify rule. Now when I delete the
>>> to-one relationship destination EO, it does not nullify, leaving bad
>>> foreign keys.
>>
>> _______________________________________________
>> Do not post admin requests to the list. They will be ignored.
>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com
>> <mailto:Webobjects-dev@lists.apple.com>)
>> Help/Unsubscribe/Update your Subscription:
>> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com
>> <https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com>
>>
>> This email sent to sam...@samkar.com
>
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
