1Z0-052 · Question #222
View the Exhibit to see the structure of the EMPLOYEES and DEPARTMENTS tables. Your organization plans to dissolve the department with department ID 30. You execute the following command to delete…
The correct answer is B. alter the foreign key constraint to include the on delete cascade option F. first, delete rows from the EMPLOYEES table for department id 30 and then delete the rows from. ORA-02292 occurs when a parent row cannot be deleted because child rows exist; you must either handle child rows first or configure the constraint to cascade deletes automatically.
Question
View the Exhibit to see the structure of the EMPLOYEES and DEPARTMENTS tables. Your organization plans to dissolve the department with department ID 30. You execute the following command to delete rows from the DEPARTMENTS table:
SQL>delete from DEPARTMENTS where DEPT_ID = 30; The command fails and displays the following error:
ERROR at line 1:
ORA-02292: integrity constraint (HR.SYS_C005374) violated - child record found Which two actions would you take to overcome this error? (Choose two.)
Exhibit
Options
- Aalter the foreign key constraint to include the cascade option
- Balter the foreign key constraint to include the on delete cascade option
- Cfirst, drop the EMPLOYEES table and then delete the rows from the DEPARTMENTS table
- Dfirst, drop the DEPARTMENTS table and then delete the rows from the EMPLOYEES table
- Efirst, delete all of the rows from EMPLOYEES table and then delete the rows from the
- Ffirst, delete rows from the EMPLOYEES table for department id 30 and then delete the rows from
How the community answered
(23 responses)- A4% (1)
- B70% (16)
- C17% (4)
- E9% (2)
Why each option
ORA-02292 occurs when a parent row cannot be deleted because child rows exist; you must either handle child rows first or configure the constraint to cascade deletes automatically.
There is no plain 'cascade option' for a foreign key constraint in Oracle; the correct syntax is specifically ON DELETE CASCADE, making this option technically inaccurate.
Altering the foreign key to include ON DELETE CASCADE causes Oracle to automatically delete all child rows in EMPLOYEES when the referenced row in DEPARTMENTS is deleted, resolving the constraint violation persistently via a DDL change to the constraint definition.
Dropping the entire EMPLOYEES table destroys all employee data across all departments, which is excessively destructive and far beyond the requirement of removing department 30.
Dropping the DEPARTMENTS table first is not possible while child rows in EMPLOYEES still reference it, and it would destroy parent data rather than resolving the constraint properly.
Deleting all rows from the EMPLOYEES table removes data for all departments, not just department 30, causing unacceptable data loss beyond the stated business requirement.
Manually deleting the EMPLOYEES rows for department_id 30 first removes all child records before the parent delete, satisfying the referential integrity constraint and allowing the DEPARTMENTS delete to succeed without modifying the constraint.
Concept tested: Oracle referential integrity and ON DELETE CASCADE foreign key constraint
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/constraint.html
Topics
Community Discussion
No community discussion yet for this question.