Born to Thrive

Examine the structure of the DEPT table:

You successfully execute the following SQL statement:
SQL>CREATE TABLE emp
(emp_no NUMBER(3) PRIMARY KEY,
emp_name VARCHAR2(25) UNIQUE,
job_id VARCHAR2(10) NOT NULL,
deptno NUMBER(2) REFERENCES dept(deptno),
salary NUMBER(10,2) CHECK (salary > 0));


For which columns would an index be generated automatically? (Choose all that apply)
A. EMP_NO
B. SALARY
C. JOB_ID
D. DEPT_NO
E. EMP_NAME

Theory:

Since the question contains a CREATE TABLE query with in-line constraints, the information required to answer it can be found as follows: CREATE TABLE and CONSTRAINTS.

• A NOT NULL constraint prohibits a database value from being null.
• A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
• A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
• A foreign key constraint requires values in one table to match values in another table.
• A check constraint requires a value in the database to comply with a specified condition.
• A REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.

• As part of the definition of an individual column or attribute. This is called INLINE specification.
• As part of the table definition. This is called OUT-OF-LINE specification.

When you specify a UNIQUE CONSTRAINT on one or more columns, Oracle implicitly creates an index on the unique key. If you are defining uniqueness for purposes of query performance, then Oracle recommends that you instead create the unique index explicitly using a CREATE UNIQUE INDEX statement. Since PRIMARY KEY CONSTRAINT combines NOT NULL and UNIQUE constraint in one declaration, Oracle as well implicitly creates an index on the primary key.

Answer:

A - Correct - Primary Key constraint automatically generates a unique index
B - Wrong - Check constraint does not automatically generate any index
C - Wrong - Not Null constraint does not automatically generate any index
D - Wrong - Foreign Key constraint does not automatically generate any index
E - Correct - Unique constraint automatically generates a unique index