1Z0-071 practice question 10 of 35

Examine this statement: CREATE TABTE orders (sarial_no NUMBER UNIQUE, order_id NUMBER PRIMARY KEY , order_date DATE NOT NULL, status VARCHAR2 (10) CHECK (status IN ('CREDIT', 'CASH')), product_id NUMBER REFERENCES products (product_id), order_ total NUMBER); On which two columns of the table will an index be created automatically?

Full set: 326 questions
Answer options
A: SERIAL_NO Correct answer
B: ORDER_DATE
C: PRODUCT_ ID
D: ORDER TOTAL
E: ORDER_ ID Correct answer
F: STATUS
Correct Answer:
A. SERIAL_NO; E. ORDER_ ID
Explanation

Oracle Database automatically creates an index to enforce the integrity of PRIMARY KEY and UNIQUE constraints. In the given CREATE TABLE statement, the orderid column is defined with a PRIMARY KEY constraint, and the serialno column is defined with a UNIQUE constraint. Therefore, a unique index will be automatically created on both of these columns. No other constraints in this statement, such as NOT NULL, CHECK, or FOREIGN KEY (on the referencing table), cause automatic index creation.

Why Incorrect

B. The NOT NULL constraint on ORDERDATE does not automatically create an index.
C. A FOREIGN KEY constraint on PRODUCTID does not automatically create an index on the referencing column.
D. The ORDERTOTAL column has no constraints that would trigger automatic index creation.
F. The CHECK constraint on the STATUS column does not automatically create an index.

References

1. Oracle Database SQL Language Reference, 23c. "CREATE TABLE". Section: constraint. "Oracle Database automatically creates an index for each unique or primary key constraint that you declare."

2. Oracle Database Concepts, 23c. "Indexes and Index-Organized Tables". Section: "How Oracle Database Maintains Indexes". "Oracle Database automatically creates an index to enforce a UNIQUE or PRIMARY KEY constraint."

Scroll to Top