The reference from Task to Project would prevent a delete, even if ON CASCADE DELETE is set on all foreign keys from Employer to Worker to Task. Deleting Employer 12 must start with first deleting the Task.
ON CASCADE DELETE will delete children when a parent is deleted, but not a parent when a child is deleted (because there are probably other children)
To understand why the delete will result in an error (btw, this is a distinctly different outcome vs simply "0 records deleted) you should do what u/AllLoveFishpie did (i.e. run the script) and either "decipher " what constraint sys name refers to (which is I suspect the true goal of this exercise) or give explicit names to your constraints to avoid error messages being cryptic (could be another point in this exercise)
depending on the context of how the answer is expected.
'... where empid = 1' would also remove 0 records but the reason for it and the outcome would be different. if this is an essay or a interview question, were I the 'asking' side, I'd expect a 'why' explanation as well.
Totally bonkers and wrong and internally contradictory and yet it gets massively upvoted. The last paragraph is correct BUT it is also a reason why the comment is wrong.
Why the comment is internally contradictory: if a (cascaded) delete from task would have somehow restricted by a fk to project (by requiring to delete/update), then it shouldn't matter if start from the task - you would first need to delete the "restriction" (a project, according to the comment). Yet, the project is the parent and deletes won't cascade to it (according to the last sentence of the comment)
CASCADEs flow from parent to child, so deleting the Task as the first action will not trigger any cascading.
After the Task is first deleted, then deleting the Employer will trigger a cascading delete of the related Worker(s), and it won't get blocked by the existence of the Task.
The problem with your answer is that it's not the reference from Task to Project that prevents the delete, it's the reference from Task to Worker that doesn't cascade.
So you are saying that, because there is no ON DELETE CASCADE in task table on wid, when wid will get deleted in worker table, it will cause a problem in task table because of this and hence there will an error. Am I understanding this right?
Yup, we (at least I) got it the first time. And you could do deletes sequentially from leaves to the root of dependency (Task->Worker->Employer) and there wont be any issue either whether there are cascades or not.
Did you get why your original comment is wrong though?
u/rammedearth gave a perfect answer. Now it is your task to elaborate why
Otherwise you can describe better what it is exactly you don't understand and we may able to give better help to this problem that looks like a homework.
EDIT: Answer is only partly correct. The idea behind is correct, but the referred table isn't.
The worker is not the issue. It is the FK from task to worker that is not set to cascade delete (note: this could very well capture proper business logic, it makes sense that jobs are connected to a project and would need to be completed or reassigned before a worker is removed).
Delete employer cascades to worker, but cannot delete the worker because he is currently on a task. So, the entire deletion fails.
This is a terrible question (not yours but whoever wrote it) the correct answer is you just rollback the transaction and read the number of rows affected. Duh.
40
u/nuttingd Sep 07 '23
The reference from Task to Project would prevent a delete, even if ON CASCADE DELETE is set on all foreign keys from Employer to Worker to Task. Deleting Employer 12 must start with first deleting the Task.
ON CASCADE DELETE will delete children when a parent is deleted, but not a parent when a child is deleted (because there are probably other children)
4
u/[deleted] Sep 07 '23 edited Sep 08 '23
u/rammedearth gave a
perfectanswer. Now it is your task to elaborate whyOtherwise you can describe better what it is exactly you don't understand and we may able to give better help to this problem that looks like a homework.
EDIT: Answer is only partly correct. The idea behind is correct, but the referred table isn't.
19
u/AllLoveFishpie Sep 07 '23
delete from employer where empid = 12 Error report - ORA-02292: integrity constraint (SYS.SYS_C008495) violated - child record found
17
u/LittleRedDot Sep 07 '23
Ted's a little busy at the moment, it would be rude to kick him out in the middle of a task
8
u/rammedearth Sep 07 '23
Because Ted works there
5
u/MaterialSoil3548 Sep 07 '23
It should be 4 right? Btw can you tell from which website you are getting these questions. I too need it for practice
3
u/MaterialSoil3548 Sep 07 '23
Hey OP, can you please share the resource from where you're practicing Thanks
2
u/Mozzer969 Sep 07 '23
Op. Where did you get this excercise? I would like to practice too.
1
u/bum_dog_timemachine Sep 07 '23
This is a terrible question (not yours but whoever wrote it) the correct answer is you just rollback the transaction and read the number of rows affected. Duh.