15.3 Inspecting Constraints, Privileges, and Comments

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

15.3 Inspecting Constraints, Privileges, and Comments

Key Takeaways Explain This Topic AI

USER_CONSTRAINTS identifies constraint definitions using single-character CONSTRAINT_TYPE codes: P (Primary Key), R (Foreign Key), U (Unique), C (Check / NOT NULL), V (With Check Option), and O (With Read Only).

NOT NULL constraints are recorded in USER_CONSTRAINTS with CONSTRAINT_TYPE = 'C' and a SEARCH_CONDITION checking that the column IS NOT NULL.

USER_CONS_COLUMNS maps constraint names to specific table columns and indicates column order in composite constraints via the POSITION column.

Security privileges and grants are inspected across USER_SYS_PRIVS (direct system privileges), USER_TAB_PRIVS (object privileges), USER_ROLE_PRIVS (granted roles), and ROLE_SYS_PRIVS / ROLE_TAB_PRIVS (role contents).

Schema documentation added using COMMENT ON TABLE and COMMENT ON COLUMN statements is stored in the data dictionary and retrieved via USER_TAB_COMMENTS and USER_COL_COMMENTS.

Last updated: August 2026

15.3 Inspecting Constraints, Privileges, and Comments

Maintaining database integrity and security requires thorough inspection of database constraints, assigned privileges, granted roles, and data dictionary documentation. On the Oracle Database SQL (1Z0-071) exam, questions frequently test your ability to decode constraint type codes, trace foreign key relationships, audit granted privileges, and manage object comments.

1. Inspecting Constraints: USER_CONSTRAINTS & USER_CONS_COLUMNS

Integrity constraints enforce business rules across database tables. Oracle separates constraint rules from their column mappings across two primary views: CONSTRAINT DICTIONARY ARCHITECTURE

• │
• ┌───────────────────────────┴───────────────────────────┐
• │ │
• USER_CONSTRAINTS USER_CONS_COLUMNS
• (Constraint Rule Definition) (Column Association)
• CONSTRAINT_NAME - CONSTRAINT_NAME
• CONSTRAINT_TYPE (P, R, U, C, V, O) - TABLE_NAME
• TABLE_NAME - COLUMN_NAME
• SEARCH_CONDITION - POSITION (1, 2, 3...)
• R_CONSTRAINT_NAME (Parent PK/UK)
• DELETE_RULE (CASCADE, SET NULL, NO ACTION)
• STATUS (ENABLED, DISABLED)
• Decoding CONSTRAINT_TYPE Codes

The CONSTRAINT_TYPE column in USER_CONSTRAINTS uses single-character codes to distinguish constraint categories:

Type Code Constraint Category Description & Behavior

P Primary Key Enforces entity integrity; requires unique, non-null values. Backed by a unique index. (Max 1 per table).

R Referential Integrity Foreign Key constraint referencing a parent Primary Key or Unique constraint.

U Unique Key Enforces uniqueness across non-null values. Backed by a unique index.

C Check Constraint Validates a boolean row condition. Also used for NOT NULL constraints.

V With Check Option Applied on a view to prevent DML from creating rows that fail the view's WHERE clause.

O With Read Only Applied on a view to disallow all DML operations through the view.

Exam Trap (Crucial!): Oracle does NOT have a distinct 'N' constraint type for NOT NULL constraints. Every NOT NULL constraint is recorded in USER_CONSTRAINTS as type 'C' (Check), with the column validation rule stored in SEARCH_CONDITION as "COLUMN_NAME" IS NOT NULL . -- Query all constraints on the EMPLOYEES table:

• SELECT constraint_name, constraint_type, status, search_condition, r_constraint_name
• FROM user_constraints
• WHERE table_name = 'EMPLOYEES'
• ORDER BY constraint_type, constraint_name;
• Tracing Foreign Keys with R_CONSTRAINT_NAME & DELETE_RULE

When CONSTRAINT_TYPE = 'R' , the following columns provide referential relationship details:

R_CONSTRAINT_NAME : The identifier of the parent table's Primary Key or Unique constraint.

R_OWNER : The schema that owns the referenced parent constraint.

DELETE_RULE : The referential action when a parent row is deleted:

• NO ACTION (Default: rejects deletion if child rows exist)
• CASCADE ( ON DELETE CASCADE : deletes referencing child rows)

SET NULL ( ON DELETE SET NULL : sets child FK columns to NULL ) -- Join child and parent constraints to trace referential relationships:

• SELECT c.table_name AS child_table,
• c.constraint_name AS fk_name,
• p.table_name AS parent_table,
• c.r_constraint_name AS parent_pk_name,
• c.delete_rule
• FROM user_constraints c
• JOIN user_constraints p
• ON c.r_constraint_name = p.constraint_name
• WHERE c.constraint_type = 'R'
• AND c.table_name = 'EMPLOYEES';
• Mapping Columns with USER_CONS_COLUMNS

To see which specific columns belong to a constraint—and their order in composite keys—join USER_CONSTRAINTS with USER_CONS_COLUMNS : SELECT uc.table_name, uc.constraint_name, uc.constraint_type,

• ucc.column_name, ucc.position
• FROM user_constraints uc
• JOIN user_cons_columns ucc
• ON uc.constraint_name = ucc.constraint_name
• WHERE uc.table_name = 'ORDER_ITEMS'
• ORDER BY uc.constraint_name, ucc.position;

POSITION : A 1-based integer indicating column ordering for composite primary/foreign/unique keys. For single-column constraints or CHECK constraints, POSITION is typically 1 or NULL .

2. Inspecting Privileges & Roles

Oracle's security model separates direct privileges from role-based privileges. Security metadata is split across specialized dictionary views: SECURITY METADATA VIEWS

• │
• ┌────────────────────────────┼────────────────────────────┐
• │ │ │
• USER_SYS_PRIVS USER_TAB_PRIVS USER_ROLE_PRIVS
• Direct system privileges - Object privileges on - Roles granted to
• granted to current user tables/views granted the current user
• Columns: USERNAME, to or by current user - Columns: USERNAME,
• PRIVILEGE, ADMIN_OPTION - Columns: GRANTEE, OWNER, GRANTED_ROLE, ADMIN_OPTION
• TABLE_NAME, PRIVILEGE, DEFAULT_ROLE
• GRANTABLE

Direct System & Object Privilege Views -- 1. Check direct system privileges (e.g., CREATE TABLE, CREATE VIEW):

• SELECT privilege, admin_option
• FROM user_sys_privs;

-- 2. Check direct object privileges granted to the current user:

• SELECT owner, table_name, privilege, grantor, grantable
• FROM user_tab_privs
• WHERE grantee = USER;

-- 3. Check object privileges granted BY the current user to others:

• SELECT grantee, table_name, privilege, grantable
• FROM user_tab_privs
• WHERE owner = USER;

ADMIN_OPTION : 'YES' if the user can grant the system privilege to other users, else 'NO' .

GRANTABLE : 'YES' if the object privilege was granted WITH GRANT OPTION , else 'NO' .

Inspecting Role Privileges: ROLE_SYS_PRIVS & ROLE_TAB_PRIVS

Because production environments grant permissions primarily via Roles , checking only USER_SYS_PRIVS and USER_TAB_PRIVS will often miss privileges inherited through roles. Developers inspect role definitions using ROLE_SYS_PRIVS and ROLE_TAB_PRIVS : -- Check roles granted to the current user:

• SELECT granted_role, admin_option, default_role
• FROM user_role_privs;

-- Inspect system privileges contained inside granted roles:

• SELECT role, privilege, admin_option
• FROM role_sys_privs
• WHERE role IN (SELECT granted_role FROM user_role_privs);

-- Inspect table privileges contained inside granted roles:

• SELECT role, owner, table_name, privilege
• FROM role_tab_privs
• WHERE role IN (SELECT granted_role FROM user_role_privs);
• 3. Adding and Querying Comments: USER_TAB_COMMENTS & USER_COL_COMMENTS

Oracle SQL supports adding descriptive documentation directly into the data dictionary for tables and columns using the COMMENT DDL statement. +-------------------------------------------------------------------------+

• | COMMENT DDL SYNTAX |
• +-------------------------------------------------------------------------+
• | Table Comment: |
• | COMMENT ON TABLE [schema.]table_name IS 'Descriptive text string'; |
• | |
• | Column Comment: |
• | COMMENT ON COLUMN [schema.]table.column IS 'Descriptive text string'; |
• | |
• | Remove Comment (Set to NULL): |
• | COMMENT ON TABLE table_name IS ''; |
• +-------------------------------------------------------------------------+

Adding Comments Example -- Add documentation to a table:

• COMMENT ON TABLE employees
• IS 'Stores core personnel, compensation, and department assignment records.';

-- Add documentation to specific columns:

• COMMENT ON COLUMN employees.salary
• IS 'Monthly base compensation in USD before bonuses and commissions.';

• COMMENT ON COLUMN employees.commission_pct
• IS 'Commission percentage (0.00 to 0.50) applicable to Sales representatives.';

-- Remove a comment by setting it to an empty string:

COMMENT ON TABLE departments IS '';

Querying Table & Column Comments -- Query table comments in the current schema:

• SELECT table_name, table_type, comments
• FROM user_tab_comments
• WHERE table_name IN ('EMPLOYEES', 'DEPARTMENTS');

-- Query column comments for a specific table:

• SELECT column_name, comments
• FROM user_col_comments
• WHERE table_name = 'EMPLOYEES'
• AND comments IS NOT NULL
• ORDER BY column_name;
• Comment Views Overview

• View Name Scope Key Columns
• USER_TAB_COMMENTS Tables and views in current schema TABLE_NAME , TABLE_TYPE ( TABLE / VIEW ), COMMENTS
• ALL_TAB_COMMENTS Accessible tables and views OWNER , TABLE_NAME , TABLE_TYPE , COMMENTS
• USER_COL_COMMENTS Columns in current schema TABLE_NAME , COLUMN_NAME , COMMENTS
• ALL_COL_COMMENTS Columns in accessible tables OWNER , TABLE_NAME , COLUMN_NAME , COMMENTS

Comprehensive Constraint, Privilege, and Comment Inspection Guide -- Full schema diagnostic query: List all table columns, comments, and NOT NULL flags

• SELECT c.column_id,
• c.column_name,
• c.data_type ||
• CASE
• WHEN c.data_type = 'NUMBER' AND c.data_precision IS NOT NULL
• THEN '(' || c.data_precision || ',' || c.data_scale || ')'
• WHEN c.data_type LIKE '%CHAR%'
• THEN '(' || c.data_length || ')'
• END AS formatted_datatype,
• c.nullable,
• cm.comments
• FROM user_tab_columns c
• LEFT JOIN user_col_comments cm
• ON c.table_name = cm.table_name
• AND c.column_name = cm.column_name
• WHERE c.table_name = 'EMPLOYEES'
• ORDER BY c.column_id;