Oracle Database 12c: SQL Fundamentals — Question 6

Examine the create table statements for the stores and sales tables.
SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address
VARCHAR2(20), start_date DATE);
SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date
DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));
You executed the following statement:

SQL> DELETE from stores -
WHERE store_id=900;
The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
Which three options ensure that the statement will execute successfully?

Answer options

• A. Disable the primary key in the STORES table.
• B. Use CASCADE keyword with DELETE statement.
• C. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
• D. Disable the FOREIGN KEY in SALES table and then delete the rows.
• E. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.

Correct answer: A, C, D

Explanation

Option C is correct because it addresses the foreign key constraint by removing dependent records in the SALES table before deleting from STORES. Option A is incorrect as disabling the primary key does not resolve foreign key dependencies. Option D is also valid but less ideal than C, as it may lead to potential data inconsistencies. Option B is incorrect since it does not apply to the existing foreign key constraint, and option E suggests a new foreign key that would not help the current situation.