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?
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.
Explanation:
A will NOT work
SQL> alter table customers disable constraint SYS_C0074074;
alter table customers disable constraint SYS_C0074074
*
ERROR at line 1:
ORA-02297: cannot disable constraint (xxx.SYS_C0074074) – dependencies exist
C, D, E are correct answers.
you have to disable the foreign key first before the primary key to make it work. If you disable the primary key first it will produce an error.
Alter table stores disable constraint store_id_pk CASCADE;
disables primary key in STORES, which in turn disables foreign key in SALES automatically.
A, C, D are correct answers to me.
E is nonsense.
If E is nonsense to you I highly recomment you reconsider working with Oracle.
The question is all about referential integrity of STORE_ID column.
Yet, E babbles about SALES_ID column.
Therefore, it’s nonsense.
Strongly agree with Leandro. You should know the ON DELETE CASCADE & ON DELETE NULL, these two constraints will influence the records in the child table. Good luck in your work!
A, C, D are correct answers.
why not C,D,E?
A is correct (it depends on D being executed first, but it is correct)
B is wrong and would fail because the referential constraint wasn’t created with on cascade.
C is correct because you would remove child records first
D is correct because would disable the referential constraint, allowing A to be performed.
E is wrong because it is using a field that has nothing to do with store id.
I disagree C, D, E is correct.
A is wrong because doesn’t ensure that the statement will execute successfully.
E is correct because if you create a FK with ON DELETE CASCADE this ensure that the statement will execute successfully.
Read question E carefully.
Suggests FK with ON DELETE CASCADE option on SALES_ID instead of STORE_ID column.
Might be mostly unthinkable to come across well-qualified americans on this area, even though you come across as like you be aware of whatever you’re talking about! Appreciate It
http://floo-id-i-tee.tumblr.com
I think that is one of the most important info for me. And i am happy studying your article. However want to commentary on few general things, The web site taste is great, the articles is actually great : D. Good activity, cheers|
I think this another one where question or the answers are mistyped.
It’s not going to be A, because doesn’t specifically do what is desired, it would prompt you to drop the foreign key (which is one of the answers)
I agree with Fabio, I think it was probably supposed to be C,D and E, but there was a typo in the answer and it should have been an FK on STORE_ID with ON DELETE CASCADE.
If not, a typo in the question and it should be pick TWO options. (C and D)
C,D,E
————-
CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY
KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);
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) on delete cascade);
DELETE from stores
WHERE store_id=1;