May 21, 2007 at 9:20 am
1)is there an alternate option for deleting child records other than cacade DELETE?
2)how can I update a child record when empid='2' is been changed to empid='6'.
May 21, 2007 at 9:33 am
The alternatives to "Cascade Delete" is to manually delete the child record(s) before deleting the parent. I often use this in Data Warehouses when I want to insure the person who is doing the delete understands all the consequences. Make a person manually delete child records insures they don't accidentally delete important data. Another alternative is doing it through a trigger but this is much less preferable to the "cascade delete" constraint unless there are additional business rules to be enforced.
The second question is a little ambiguous. Is empid a foreign key from the child record? If so and you did not cascade the update then the record is orphaned and you are probably SOL on updating it later. Referential integrity constraints should be used to prevent that condition from occurring either by preventing the change in the key value or cascading it.
James.
May 21, 2007 at 9:38 am
empid is a PK in parent table and FK in child table.
May 21, 2007 at 9:52 am
What is the constraint? (constraint fk_constrname foreign key (colname) references parenttablename (colname) [on update cascade] [on delete cascade]
If you don't have a constraint then its only an implied foreign key (not a real one) and if you do and didn't specify the update/delete portion then the DB should prevent the update, otherwise the update cascades and and then you can update the child using the new empid value.
NOTE: The value of empid in the child is dependent on several factors. For simplicity lets assume you have update cascade set.
1) if you are on the same connection as the initial update to parent then you should be able to reference the child using the new empid (even if within a transaction).
2) if on a different connection then you will have to wait for the transaction to complete and then use the new empid. Probably can not reference child with old empid because a lock should prevent any updates to child until transaction of parent completes.
James.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply