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?
- N
- C
- U
- 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';- CUSTOMERS
- CUSTOMERS_PK
- CUSTOMER_ID
- 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';- RESTRICT
- NO ACTION
- CASCADE
- 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?
- N
- Y
- NULL
- 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.