View the Exhibit and examine the structure of the ORDERS table:
The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT NULL constraint.
Evaluate the following statement:
INSERT INTO (SELECT order_id,order_date,customer_id FROM ORDERS
WHERE order_total = 1000
WITH CHECK OPTION) VALUES (13, SYSDATE, 101);
What would be the outcome of the above INSERT statement?
A.
It would execute successfully and the new row would be inserted into a new temporary table created by the subquery.
B.
It would execute successfully and the ORDER_TOTAL column would have the value 1000 inserted automatically in the new row.
C.
It would not execute successfully because the ORDER_TOTAL column is not specified in the SELECT list and no value is provided for it.
D.
It would not execute successfully because all the columns from the ORDERS table should have been included in the SELECT list and values should have been provided for all the columns.
ORA-01402: view WITH CHECK OPTION where-clause violation
Cause: An INSERT or UPDATE statement was attempted on a view created with the CHECK OPTION. This would have resulted in the creation of a row that would not satisfy the view’s WHERE clause.
Action: Examine the view’s WHERE clause in the dictionary table VIEWS. If the current view does not have the CHECK OPTION, then its FROM clause must reference a second view that is defined using the CHECK OPTION. The second view’s WHERE clause must also be satisfied by any INSERT or UPDATE statements. To insert the row, it may be necessary to insert it directly into the underlying table, rather than through the view.
The query would insert successfully if written this way:
INSERT INTO (SELECT order_id,order_date,customer_id, order_total FROM ORDERS
WHERE order_total = 1000
WITH CHECK OPTION) VALUES (15, SYSDATE, 101, 1000);
INSERT INTO (SELECT order_id,order_date,customer_id, order_total FROM ORDERS
WHERE order_total = 1000
WITH CHECK OPTION) VALUES (777, SYSDATE, 100, 1002); — this violate constraint —
INSERT INTO (SELECT order_id,order_date,customer_id, order_total FROM ORDERS
WHERE order_total = 1000
WITH CHECK OPTION) VALUES (777, SYSDATE, 100, 1000); — this works —
If this was the violating unique constraint. It will also be rollbacked at the commit.