Oracle Database 12c: Installation and Administration — Question 77

The HR.DEPARTMENTS table is the parent of the HR.EMPLOYEES table. The EMPLOYEES.DEPARTMENT_ID column has a foreign key constraint with the ON option that refers to the DEPARTMENTS.DEPARTMENT_ID column. An index exists on the DEPARTMENTS.DEPARTMENT_ID column. A

DELETE CASCADE -
transaction deletes a primary key in the DEPARTMENTS table, which has child rows in the EMPLOYEES table.
Which statement is true?

Answer options

• A. The transaction acquires a table lock only on the DEPARTMENTS table until the transaction is complete.
• B. The transaction acquires a table lock on the DEPARTMENTS table. This lock enables other sessions to query but not update the DEPARTMENTS table until the transaction on the DEPARTMENTS table is complete.
• C. The transaction acquires a table lock on the EMPLOYEES table. This lock enables other sessions to query but not update the EMPLOYEES table until the transaction on the DEPARTMENTS table is complete.
• D. Only the rows that are deleted in the DEPARTMENTS and EMPLOYEES tables are locked until the transactions on the DEPARTMENTS table is complete.

Correct answer: C

Explanation

The correct answer is C because when a DELETE CASCADE is executed on the parent DEPARTMENTS table, it also locks the child EMPLOYEES table to prevent updates during the transaction. Options A and B incorrectly state that the lock only applies to the DEPARTMENTS table, while option D is incorrect because it suggests that only the deleted rows are locked, rather than the entire EMPLOYEES table.