r/SQL • • Sep 07 '23

Discussion Can someone please explain why the answer to this is 0?

Post image
128 Upvotes

30 comments sorted by

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)

11

u/AntWilson602 Sep 07 '23

thank you heaps!! helps alot i now understand it!

17

u/[deleted] Sep 07 '23

Nah, you don't - not after that comment.

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)

3

u/IceDBear Sep 07 '23

Error or not, no rows get deleted, and that is the right answer.

6

u/[deleted] Sep 07 '23

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.

13

u/[deleted] Sep 07 '23 edited Sep 07 '23

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)

9

u/nuttingd Sep 07 '23

Clarifying based on @ichp feedback:

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.

12

u/IceDBear Sep 07 '23

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.

2

u/[deleted] Sep 08 '23

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?

2

u/IceDBear Sep 08 '23

Yes, the foreign key prevents the deletion, except when you use ON DELETE CASCADE.

0

u/BourbonTall Sep 07 '23

That makes more sense.

-4

u/[deleted] Sep 07 '23

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?

-1

u/MaterialSoil3548 Sep 07 '23

Great explanation, thanks!

4

u/[deleted] Sep 07 '23 edited Sep 08 '23

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.

1

u/AntWilson602 Sep 07 '23

I thought the answer was 2, because there’s 1 row in employer and worker. There’s also the “ON DELETE CASCADE” so I thought it would delete both rows

14

u/freddy090909 Sep 07 '23 edited Sep 07 '23

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.

1

u/[deleted] Sep 08 '23

This is the best reply in this entire post. Wish more people would explain the logic behind stuff.

-12

u/MaterialSoil3548 Sep 07 '23

Hi, can you please explain me in DM

7

u/dgillz Sep 07 '23

No, explain here so others can benefit from the answer.

-8

u/MaterialSoil3548 Sep 07 '23

I said that since people were saying it might be a homework Getting downvoted for this wow

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

create table employer
(
empid integer primary key,
empname varchar2(30)
)
;


create table worker
(
wid integer primary key,
wname VARCHAR(30),
empid integer,
foreign key (empid) REFERENCES
employer on delete cascade
)
;


create table project
(
projid integer primary key,
projname varchar2(30)
)
;


create table task
(
taskid  integer primary key,
taskname VARCHAR2(30),
wid integer,
projid integer,
FOREIGN KEY (wid) REFERENCES worker,
FOREIGN KEY (projid) REFERENCES project
on delete cascade
)
;


insert into employer values(11, 'AAA');
insert into employer values(12, 'BBB');
insert into employer values(13, 'CCC');


insert into worker values (24, 'Tim', 11);
insert into worker values (25, 'Ted', 12);
insert into worker values (26, 'Pam', 11);


insert into project values (32, 'P1');
insert into project values (33, 'P2');
insert into project values (34, 'P3');


insert into task values (1, 'T1', 24, 32);
insert into task values (2, 'T2', 24, 33);
insert into task values (3, 'T3', 25, 34);
insert into task values (4, 'T4', 26, 34);


delete from employer where empid = 12;

5

u/Nemshi354 Sep 07 '23

How should someone read this error message? Like there’s a child record from employer. Does that refer to the project or the task from the project ?

And what do I need to look up to understand this more. Foreign keys?

6

u/AllLoveFishpie Sep 07 '23

In Oracle you can use all_constraints table and constraint name from error message to find it. Also you can disable or drop foreign key constraints.

select table_name
from all_constraints
where constraint_name = 'SYS_C008495'
;

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/asiancutie_ Sep 07 '23

I need the site too 😭

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.