Oracle User Table Constraints Query with dba_constraints

calendar Sep 1, 2024 / · 10 min read · Oracle SQL Database Constraints Data Integrity DBA_CONSTRAINTS Primary Key Foreign Key Oracle Administration  ·
Share on: linkedin copy

Oracle Database: Show All Table Constraints for a User using dba_constraints

Purpose

This Oracle SQL query is designed to retrieve a comprehensive list of all constraints associated with tables owned by a specific user in your Oracle database. Constraints are essential rules that ensure data integrity and consistency. This query offers a clear and organized view of these constraints, aiding in database management and troubleshooting.

Constraint metadata lives in three overlapping dictionary views, and knowing which one to reach for matters before the query even runs. USER_CONSTRAINTS shows only the constraints owned by the connected schema. ALL_CONSTRAINTS adds every constraint the connected account has been granted visibility on, whether it owns the table or not. DBA_CONSTRAINTS returns every constraint in the entire database regardless of owner, but only to an account holding a privilege such as SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY. A developer working inside their own schema reaches for USER_CONSTRAINTS. A DBA auditing a specific application account, comparing constraint coverage across two schemas before a migration, or reviewing a third-party schema before granting it production access needs the wider DBA_CONSTRAINTS view with an explicit OWNER filter — which is exactly what the query below does.

DBA_CONSTRAINTS alone answers "does this constraint exist, and what type is it" — it does not answer "what columns does it cover." A full constraint audit typically pairs this query with DBA_CONS_COLUMNS, which maps each constraint name to its underlying column list and column position within a multi-column key. This post focuses on the constraint inventory step: a per-table, per-type breakdown a DBA can scan in seconds before drilling into column-level detail on any constraint that looks wrong.

Sample SQL Command

 1set lines 100 pages 999
 2break on table_name
 3select  table_name
 4,       decode(constraint_type,
 5                'C', 'Check',
 6                'O', 'R/O View',
 7                'P', 'Primary',
 8                'R', 'Foreign',
 9                'U', 'Unique',
10                'V', 'Check view') type
11,       nvl(index_name, R_CONSTRAINT_NAME) "IDX"
12from    dba_constraints
13where   owner like '&user'
14order   by table_name
15,       decode(constraint_type,
16        'P','0','R','1','U','2','C','3','O','4','V','5')
17/

Code Breakdown

  1. set lines 100 pages 999: Sets the output formatting for better readability.
  2. break on table_name: Organizes the results by grouping them based on table names.
  3. select table_name, decode(constraint_type, ... ) type, nvl(index_name, R_CONSTRAINT_NAME) "IDX": Selects three columns:
    • table_name: The name of the table the constraint is applied to.
    • type: Decodes the constraint type code (C, O, P, R, U, V) into human-readable descriptions (Check, Read-Only View, Primary Key, Foreign Key, Unique, Check View).
    • "IDX": Shows the associated index name (if available) or the name of the referenced constraint for foreign keys.
  4. from dba_constraints: Queries the dba_constraints data dictionary view, which stores information about all constraints in the database.
  5. where owner like '&user': Filters the results to only include constraints for tables owned by the specified user. You'll be prompted to enter the username when running this query.
  6. order by table_name, decode(constraint_type, ... ): Sorts the output first by table name and then by constraint type, following a specific priority order (Primary Key, Foreign Key, Unique, Check, Read-Only View, Check View).

The six constraint type codes

CONSTRAINT_TYPE in DBA_CONSTRAINTS is a single-character code, and this query's DECODE translates all six values Oracle defines. P and U are the structural keys — Primary and Unique — each backed by a supporting index. R is a Foreign Key, referencing a primary or unique key on another table (or the same table, for a self-referencing hierarchy). C is a Check constraint, which includes both explicit CHECK (...) clauses and the implicit NOT NULL constraints Oracle generates automatically for NOT NULL column definitions — a detail worth knowing, since a table with no explicit check logic can still show several C rows in the output. O and V are the rarest two: they apply to views rather than base tables — O marks a read-only view constraint, V marks a view created WITH CHECK OPTION. Seeing an O or V row against something you expected to be a plain table is a signal to confirm the object type before investigating further.

Why NVL combines two different columns

INDEX_NAME and R_CONSTRAINT_NAME are populated in mutually exclusive situations, which is what makes wrapping them in a single NVL safe rather than confusing. For a Primary Key or Unique constraint, INDEX_NAME holds the name of the unique index Oracle uses to enforce it, and R_CONSTRAINT_NAME is null. For a Foreign Key, INDEX_NAME is null — a foreign key does not own its own enforcing index — and R_CONSTRAINT_NAME instead holds the name of the parent constraint it references, typically the primary key on the referenced table. NVL(index_name, R_CONSTRAINT_NAME) collapses both cases into a single "IDX" column: for a P or U row you're looking at an index name, and for an R row you're looking at the name of the constraint being referenced. For C, O, and V rows, both source columns are null and the "IDX" column is blank, which is expected.

The DECODE sort priority explained

The second DECODE in the ORDER BY clause assigns each constraint type a sort weight — Primary first (0), then Foreign (1), Unique (2), Check (3), Read-Only View (4), Check view (5) — and Oracle sorts numerically on that weight within each table_name group. This ordering mirrors how a DBA actually reviews a table's constraints: confirm the primary key exists first, check what it's referenced by (foreign keys), then uniques, then the broader set of check conditions, with the rare view-level constraints trailing at the bottom. Without this explicit weighting, the default alphabetical sort on CONSTRAINT_TYPE would put Check (C) before Foreign (R) before Primary (P) — alphabetically correct but operationally backwards for a quick scan.

Key Points

  • Data Dictionary View: Leverages the dba_constraints view to access constraint metadata.
  • Constraint Type Decoding: Translates cryptic constraint type codes into meaningful descriptions.
  • Index Information: Includes the index name associated with the constraint, if applicable.
  • User Filtering: Allows you to focus on constraints related to a specific user's tables.
  • Organized Output: Presents the results in a sorted and easy-to-interpret format.
  • Note: This query requires you to have the necessary privileges to access the dba_constraints view. If you encounter permission issues, consider using the all_constraints or user_constraints views, which have less restrictive access requirements.
  • STATUS is not in this query but is worth adding. DBA_CONSTRAINTS.STATUS reports ENABLED or DISABLED, and VALIDATED reports VALIDATED or NOT VALIDATED. A constraint can exist, be enabled, and still not be validated against existing data — a state worth knowing about before trusting a foreign key blindly.
  • break on table_name only suppresses repeated values, it doesn't group rows physically. The underlying ORDER BY table_name is what makes the visual grouping possible; removing the ORDER BY while keeping the BREAK produces a broken, non-grouped display.

Insights and Best Practices

Understanding the constraints on your tables is crucial for database design, maintenance, and troubleshooting. This query empowers you to quickly identify the types of constraints applied to each table. It helps you pinpoint potential issues like foreign key violations or conflicts with unique constraints.

Add STATUS and VALIDATED for a fuller audit

The base query answers "what constraints exist and what type." It does not answer "are they actually enforcing anything right now." Extending the SELECT list with status and validated turns the same query into a health check: a P or U constraint with STATUS = 'DISABLED' means the structural key isn't currently protecting the table at all, and an R constraint with VALIDATED = 'NOT VALIDATED' means Oracle has not confirmed every existing row satisfies the relationship — a common state right after a bulk load that used ALTER TABLE ... ENABLE NOVALIDATE to skip the validation pass for speed.

Pair with DBA_CONS_COLUMNS for column-level detail

This query is deliberately a table-and-type-level inventory, not a column-level one. Once a specific constraint looks worth investigating — an unexpected U on a table that shouldn't have a uniqueness rule, or an R pointing at a table you didn't know was referenced — joining to DBA_CONS_COLUMNS on CONSTRAINT_NAME and OWNER returns the exact column or columns involved, in their defined position for multi-column keys. Keep the two queries separate: an inventory query that tries to also return column lists produces one row per column instead of one row per constraint, which breaks the clean per-table grouping this query is built for.

Run it before any schema comparison or migration

Before comparing constraint coverage between a source and target schema — a common pre-migration or pre-refresh step — running this query against both schemas and diffing the output is faster and more reliable than comparing DDL scripts by eye. Differences in constraint type, or a constraint present on one side and missing on the other, surface immediately in the type-sorted output.

Watch for orphaned foreign keys after partial exports

A Foreign Key constraint whose "IDX" column (via R_CONSTRAINT_NAME) points at a constraint name that doesn't independently appear as a P or U row elsewhere in the output for that owner is a sign the referenced parent table or key lives in a different schema, or has been dropped. This is a fast visual check worth doing on any export or clone that only copied part of a larger schema.

When to Run This Query

  • Auditing a schema you've just inherited or been handed access to, where the constraint layout isn't documented anywhere.
  • Comparing constraint coverage between two schemas before a migration, refresh, or environment promotion.
  • Investigating an ORA-02291 (parent key not found) or ORA-02292 (child record found) error, to see the full set of foreign keys on the tables involved before tracing the specific violation.
  • Reviewing a third-party or vendor-supplied schema before granting it production access, to confirm the expected primary and unique keys actually exist.
  • Confirming a bulk load or ALTER TABLE change didn't leave a constraint disabled, by extending the query with STATUS and VALIDATED.
  • Documenting a schema's structural rules as part of a change-control or compliance review.

Troubleshooting Common Issues

ORA-00942: table or view does not exist on dba_constraints. This means the connected account lacks the privilege to see the DBA_-prefixed view — typically SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY. If a DBA grant isn't available, substitute ALL_CONSTRAINTS (visible constraints the account has access to) or USER_CONSTRAINTS (constraints owned by the connected schema only) and drop the owner predicate accordingly, since USER_CONSTRAINTS has no OWNER column to filter on.

The query returns no rows even though the user clearly owns tables. WHERE owner LIKE '&user' is case-sensitive against the stored value, and Oracle stores unquoted identifiers, including usernames, in upper case by default. Entering a lower-case username at the prompt silently returns zero rows rather than an error. Enter the username in upper case, or wrap the predicate in UPPER(owner) LIKE UPPER('&user') to remove the sensitivity.

The "IDX" column is blank for a row you expected to have a value. This is expected for C, O, and V constraint types, since neither INDEX_NAME nor R_CONSTRAINT_NAME applies to a check or view constraint. It is not an error in the query.

A Foreign Key's "IDX" value doesn't look like an index name. That's by design — for R rows, the column is populated from R_CONSTRAINT_NAME, which is the name of the referenced constraint (usually a primary key), not an index on the table you queried. Look up that constraint name directly, or on the parent table's own row set, to find the index actually enforcing it.

References

Posts in this series