Using the ON DELETE CASCADE option on a foreign key

Question

When using ON DELETE CASCADE on a foreign key, does the cascade option apply only to the entries in the same table?

Consider a table Employee created with the following option:

CONSTRAINT EMPSUPERFK FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn) ON DELETE CASCADE ON UPDATE CASCADE,

What happens when the following command is run on the database state shown in Figure 5.6? DELETE EMPLOYEE WHERE Lname = ‘Borg’

The answer was that all the employees having Borg as their manager will be deleted and all the employees having the employee deleted as their manager will be deleted as well, etc. (note that the solution is not from the book's author which is why I am asking here).

I thought that everything that includes this employee would be deleted as well, for example, the department "Headquarters" as he is the manager, etc. What's right?

Answer 1

You are showing a self-referencing foreign key, that relates the manager of the employee to the corresponding record in the same table that represents the manager: that's a hierarchical structure.

When a parent is deleted (that's what Borg is), then all children are automatically dropped, recursively. All employees in the table do refer directly or indirectly to Borg, so the all records of the table will be deleted.

I thought that everything that includes this employee would be deleted as well, for example, the department "Headquarters" as he is the manager, etc.. what's right?

The information that is given in the question does not suggest any such thing. For this to happen, there should typically be a foreign key set up in table department to relate column mgr_ssn to column ssn in table employee, with the on delete cascade option. Although it might exist, there is no indication that it does in the question that is asked.