[Reactor For CF] cascadeDelete and cascadeSave (but mostly delete)

[Jared Rypka-Hauer]

How you handle this process for cascade updates or deletes depends entirely on where the linking primary key values are being stored and if they're nullable. If Address.userId is how the users are being linked to the address records, then you should be able to delete addresses by UserId without ever touching the rows in the user table at all, and cascades for deletes from User.delete() to include Address are more than feasible (they could be left optional, but one wonders why you'd want to have an orphan address at all anyway!) If User.AddressId is how the users are being linked to their address records, then you're dealing with a whole other scenario... many users could be using the same address, but, depending on how the database is built, you may not be able to delete an Address without deleting the linked user records first. I don't think it can be handled effectively without recognizing which side of the relationship owns the primary key that's being deleted... if UserId is the pkey, then and only then should cascadeDelete or cascadeSave be allowed, because the User object can't control data that isn't associated with it's primary key. So if the Address table stores UserID values, then sure, User can delete records in the Address table. However, if User stores AddressID values, then no, the User shouldn't be allowed to delete the Address records. In the case of link tags, either object should be able to blow away records in the linking table, but not in the table on the far side of the relationship... i.e. User and Address can both delete UserAddress records, but User cannot reach across the linkage and delete Addresses, nor can Address delete Users. Here's what seems to me to make the most sense: Where User hasOne Address (and Address is linked via User.UserId): User.getAddress().delete() deletes address User.delete() deletes the user and possibly the Address depending on a cascadeDelete boolean, and if there's an fkey constraint you won't be able to delete User without first deleting all the Addresses first anyway! Where User hasMany Addresses (and Address is linked via User.UserID in a direct relationship): User.getAddressesIterator().deleteAll() deletes all address records by userId.  User.delete() deletes the User record and matching Address records (where cascadeDelete=true) Where User hasMany *OR* hasOn Addresse(s) (and Address accessed via a linking table): User.getAddressIterator().deleteAll() and User.getAddress().delete() deletes all intermediate table records but doesn't touch the other side of the join. Link does NOT support cascadeDelete beyond the relationship with the link table. I can't think of a situation where you'd want to delete the Address and the UserAddress at the same time from the User object or anywhere else... the whole intention of a link table is to allow n-records in either direction. Does this make sense so far? Leaving orphans in the data is a bad idea in general, so I think for some relationships the cascade operations need to be enforced and for others they will be mandatory by default because of foreign key constraints. In the end, though, it's going to be on the developer to make sure that their Reactor config files line up well with the database so that the method calls produce the desired behaviors. You can only help the brain-dead so much before you have to call the time. Laterz, J ------------------------------------------------ Jared C. Rypka-Hauer Continuum Media Group LLC http://www.web-relevant.com Member, Team Macromedia - ColdFusion