nerdexam
Oracle

1Z0-052 · Question #25

Examine the following command that is used to create a table: SQL> CREATE TABLE orders ( oid NUMBER(6) PRIMARY KEY, odate DATE, ccode NUMBER (6), oamt NUMBER(10,2) ) TABLESPACE users; Which two…

The correct answer is B. A NOT NULL constraint is created on the OID column D. The ORDERS table and a unique index are created in the USERS tablespace. Creating a PRIMARY KEY constraint in Oracle implicitly enforces NOT NULL on the key column and creates a unique index in the same tablespace as the table.

Managing Schema Objects

Question

Examine the following command that is used to create a table:

SQL> CREATE TABLE orders ( oid NUMBER(6) PRIMARY KEY, odate DATE, ccode NUMBER (6), oamt NUMBER(10,2) ) TABLESPACE users; Which two statements are true about the effect of the above command? (Choose two.)

Options

  • AA CHECK constraint is created on the OID column
  • BA NOT NULL constraint is created on the OID column
  • CThe ORDERS table is the only object created in the USERS tablespace
  • DThe ORDERS table and a unique index are created in the USERS tablespace
  • EThe ORDERS table is created in the USERS tablepsace and a unique index is created on the OID

How the community answered

(29 responses)
  • A
    7% (2)
  • B
    76% (22)
  • C
    14% (4)
  • E
    3% (1)

Why each option

Creating a PRIMARY KEY constraint in Oracle implicitly enforces NOT NULL on the key column and creates a unique index in the same tablespace as the table.

AA CHECK constraint is created on the OID column

A CHECK constraint is never implicitly created by a PRIMARY KEY definition; PRIMARY KEY enforces uniqueness and NOT NULL, not a custom value check.

BA NOT NULL constraint is created on the OID columnCorrect

A PRIMARY KEY constraint in Oracle automatically enforces NOT NULL on the OID column because a primary key by definition cannot contain null values, so Oracle implicitly adds a NOT NULL constraint.

CThe ORDERS table is the only object created in the USERS tablespace

A unique index object is also created in the USERS tablespace to enforce the primary key, so ORDERS is not the only object created there.

DThe ORDERS table and a unique index are created in the USERS tablespaceCorrect

When a PRIMARY KEY is defined without a separate USING INDEX TABLESPACE clause, Oracle creates the enforcing unique index in the same tablespace as the table. Since TABLESPACE users is specified, both the ORDERS table and its unique index are created in USERS.

EThe ORDERS table is created in the USERS tablepsace and a unique index is created on the OID

This choice omits that the unique index is also placed in the USERS tablespace, implying it may reside elsewhere, which is incorrect when no separate USING INDEX TABLESPACE clause overrides the table's tablespace.

Concept tested: Oracle PRIMARY KEY implicit constraints and index placement

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html

Topics

#PRIMARY KEY constraint#NOT NULL constraint#unique index#tablespace storage

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-052 Practice