Stepwise-Lab

Data dictionary: 1Z0-071 practice questions

Questions in the style of exam 1Z0-071 on this topic. Answer each one first, then open its answer and explanation.

What this topic covers

  • Using data dictionary views

Practice questions

1. In USER_CONSTRAINTS, which CONSTRAINT_TYPE does a NOT NULL constraint have?

  1. N
  2. C
  3. U
  4. R
Show the answer

Answer: B

NOT NULL constraints are stored as check constraints, type C. P is primary key, U unique, R referential (foreign key).

2. The tables were created as shown. What does this query return?

-- CREATE TABLE customers (
--   customer_id  NUMBER(6)  CONSTRAINT customers_pk PRIMARY KEY, ...);
-- CREATE TABLE orders (
--   order_id     NUMBER(8)  CONSTRAINT orders_pk PRIMARY KEY,
--   customer_id  NUMBER(6)  CONSTRAINT orders_customer_fk REFERENCES customers (customer_id), ...);
SELECT r_constraint_name
FROM   user_constraints
WHERE  constraint_name = 'ORDERS_CUSTOMER_FK';
  1. CUSTOMERS
  2. CUSTOMERS_PK
  3. CUSTOMER_ID
  4. ORDERS
Show the answer

Answer: B

A foreign key row stores the name of the referenced primary or unique key constraint in R_CONSTRAINT_NAME: CUSTOMERS_PK, not the parent table CUSTOMERS or the column CUSTOMER_ID. ORDERS is the foreign key's own TABLE_NAME. Join USER_CONSTRAINTS to itself on r_constraint_name = constraint_name to get the referenced table.

3. The foreign key was created as shown. What does this query return?

-- CREATE TABLE employees (
--   employee_id  NUMBER(6)  CONSTRAINT employees_pk PRIMARY KEY,
--   ...
--   manager_id   NUMBER(6)  CONSTRAINT employees_manager_fk REFERENCES employees (employee_id),
--   ...);
SELECT delete_rule
FROM   user_constraints
WHERE  constraint_name = 'EMPLOYEES_MANAGER_FK';
  1. RESTRICT
  2. NO ACTION
  3. CASCADE
  4. SET NULL
Show the answer

Answer: B

A foreign key created without ON DELETE shows DELETE_RULE 'NO ACTION': deleting a parent with children fails with ORA-02292. ORDER_ITEMS_ORDER_FK, created with ON DELETE CASCADE, shows 'CASCADE', and ON DELETE SET NULL would show 'SET NULL'. Oracle has no ON DELETE RESTRICT clause.

4. employees.employee_id was declared NUMBER(6) CONSTRAINT employees_pk PRIMARY KEY, with no NOT NULL. What does NULLABLE show for it in USER_TAB_COLUMNS?

  1. N
  2. Y
  3. NULL
  4. P
Show the answer

Answer: A

A primary key implies NOT NULL, so the column is reported as not nullable (N) even though no separate NOT NULL constraint appears in USER_CONSTRAINTS. NULLABLE only takes the values Y and N.