DBA_CONSTRAINTS - Query Oracle Table Constraints

DBA_CONSTRAINTS

Overview

DBA_CONSTRAINTS describes every constraint defined on every table in the database, across all schemas. It covers primary keys (P), unique constraints (U), check constraints (C — including the implicit NOT NULL check Oracle creates for NOT NULL columns), foreign keys (R for Referential integrity), and view constraints (O and V). DBAs and developers query it to verify data integrity rules are in place, to understand the referential dependency graph between tables before dropping or truncating them, to check constraint status (ENABLED/DISABLED, VALIDATED/NOVALIDATED), and to diagnose ORA-02291 and ORA-02292 foreign key violations.

View Type: Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_CONSTRAINTS or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
OWNER	VARCHAR2(128)	Schema owning the table on which the constraint is defined
CONSTRAINT_NAME	VARCHAR2(128)	Name of the constraint; Oracle generates system names like SYS_C007654 for unnamed constraints
CONSTRAINT_TYPE	VARCHAR2(1)	P (primary key), U (unique), C (check, including NOT NULL), R (foreign key / referential), O (read-only view), V (check option on view)
TABLE_NAME	VARCHAR2(128)	Table (or view) on which the constraint is defined
SEARCH_CONDITION	LONG	Text of the check condition for C-type constraints
R_OWNER	VARCHAR2(128)	Schema owning the referenced table for foreign key constraints
R_CONSTRAINT_NAME	VARCHAR2(128)	Name of the primary or unique key constraint being referenced by a foreign key
DELETE_RULE	VARCHAR2(9)	CASCADE, SET NULL, or NO ACTION for foreign key delete behaviour
STATUS	VARCHAR2(8)	ENABLED or DISABLED
DEFERRABLE	VARCHAR2(14)	DEFERRABLE or NOT DEFERRABLE
DEFERRED	VARCHAR2(9)	IMMEDIATE or DEFERRED
VALIDATED	VARCHAR2(13)	VALIDATED (all data complies) or NOT VALIDATED
GENERATED	VARCHAR2(14)	USER NAME (explicitly named) or GENERATED NAME (system-generated name)
RELY	VARCHAR2(4)	RELY if Oracle can rely on this constraint for query rewriting even if it is not enforced
LAST_CHANGE	DATE	Date the constraint was last enabled or disabled
INDEX_OWNER	VARCHAR2(128)	Owner of the index enforcing a primary key or unique constraint
INDEX_NAME	VARCHAR2(128)	Name of the index enforcing a primary key or unique constraint
INVALID	VARCHAR2(7)	INVALID if the constraint is in an invalid state
VIEW_RELATED	VARCHAR2(14)	DEPEND ON VIEW if the constraint depends on a view

Essential Queries

Basic Usage

List all constraints on a specific table with their type, status, and validation state:

SELECT
    constraint_name,
    constraint_type,
    status,
    validated,
    deferrable,
    deferred,
    rely,
    last_change,
    index_name
FROM
    dba_constraints
WHERE
    owner      = 'APP_OWNER'
    AND table_name = 'ORDERS'
ORDER BY
    constraint_type,
    constraint_name;

Monitoring Query

Find all disabled or not-validated constraints across non-Oracle schemas — these are data integrity gaps that should be reviewed:

SELECT
    owner,
    table_name,
    constraint_name,
    constraint_type,
    status,
    validated,
    last_change
FROM
    dba_constraints
WHERE
    owner NOT IN (
        SELECT username FROM dba_users WHERE oracle_maintained = 'Y'
    )
    AND constraint_type IN ('P', 'U', 'R', 'C')
    AND (status = 'DISABLED' OR validated = 'NOT VALIDATED')
ORDER BY
    owner,
    table_name,
    constraint_type;

Combined with Other Views

Join DBA_CONSTRAINTS with DBA_CONS_COLUMNS to show each constraint alongside the columns it covers:

SELECT
    c.owner,
    c.table_name,
    c.constraint_name,
    c.constraint_type,
    c.status,
    c.validated,
    c.delete_rule,
    cc.column_name,
    cc.position
FROM
    dba_constraints  c
    JOIN dba_cons_columns cc
        ON  cc.owner           = c.owner
        AND cc.constraint_name = c.constraint_name
WHERE
    c.owner = 'APP_OWNER'
ORDER BY
    c.table_name,
    c.constraint_name,
    cc.position;

Foreign Key Dependency Map

Show all foreign key relationships in a schema — essential before dropping, truncating, or partitioning parent tables:

SELECT
    c.owner                                     AS fk_owner,
    c.table_name                                AS fk_table,
    cc.column_name                              AS fk_column,
    c.constraint_name                           AS fk_constraint,
    c.r_owner                                   AS pk_owner,
    rc.table_name                               AS pk_table,
    rcc.column_name                             AS pk_column,
    c.delete_rule,
    c.status
FROM
    dba_constraints  c
    JOIN dba_cons_columns  cc  ON  cc.owner           = c.owner
                               AND cc.constraint_name = c.constraint_name
    JOIN dba_constraints   rc  ON  rc.owner           = c.r_owner
                               AND rc.constraint_name = c.r_constraint_name
    JOIN dba_cons_columns  rcc ON  rcc.owner          = rc.owner
                               AND rcc.constraint_name = rc.constraint_name
                               AND rcc.position        = cc.position
WHERE
    c.constraint_type = 'R'
    AND c.owner = 'APP_OWNER'
ORDER BY
    c.table_name,
    c.constraint_name,
    cc.position;

System-Named Constraint Report

Identify constraints that use Oracle-generated names (SYS_C…) which should be renamed to meaningful names for maintainability:

SELECT
    owner,
    table_name,
    constraint_name,
    constraint_type,
    search_condition,
    status
FROM
    dba_constraints
WHERE
    generated = 'GENERATED NAME'
    AND constraint_type IN ('P', 'U', 'R')
    AND owner NOT IN (
        SELECT username FROM dba_users WHERE oracle_maintained = 'Y'
    )
ORDER BY
    owner,
    table_name,
    constraint_type;

Common Use Cases

• Pre-truncate or pre-drop checks — Before truncating a parent table, query DBA_CONSTRAINTS for all R-type constraints where R_CONSTRAINT_NAME references the parent’s primary key, to identify which child tables must be handled first
• Data integrity audit — After a bulk load or data migration, find all constraints with STATUS = DISABLED or VALIDATED = NOT VALIDATED and re-enable or validate them to confirm data quality
• ORA-02291 / ORA-02292 diagnosis — When foreign key violations are raised, use the R_CONSTRAINT_NAME to quickly identify which parent table and column is being referenced and what values are missing
• Schema documentation — Export the full constraint map for a schema to document the entity-relationship model as it actually exists in the database, not just as it was designed
• Unnamed constraint cleanup — System-generated constraint names (SYS_C…) make error messages and migration scripts harder to understand; use DBA_CONSTRAINTS to find and rename them
• RELY constraint validation — Data warehouse schemas often use RELY-based constraints for query rewrite optimisation; verify that these are correctly set without VALIDATED status on partitioned fact tables

Related Views

• DBA_CONS_COLUMNS — The columns included in each constraint; must be joined to DBA_CONSTRAINTS to see which columns a constraint covers
• DBA_DEPENDENCIES — Object dependencies; foreign key constraints create dependencies between tables that DBA_DEPENDENCIES also reflects
• DBA_INDEXES — The index enforcing a primary key or unique constraint is identified by INDEX_NAME in DBA_CONSTRAINTS
• DBA_TABLES — Table-level metadata; join on OWNER and TABLE_NAME to add row counts or tablespace information to the constraint report
• ALL_CONSTRAINTS — Session-scoped version showing constraints on tables accessible to the current user

Version Notes

• Oracle 8i: RELY column added to support query rewrite in data warehouse configurations
• Oracle 9i: DEFERRABLE and DEFERRED columns added for transaction-deferred constraint checking
• Oracle 10g: No structural changes; online constraint operations improved
• Oracle 12c: VALIDATED = NOT VALIDATED behaviour clarified for large partitioned tables; CONSTRAINT_TYPE expanded to support virtual column constraints
• Oracle 19c: No structural changes; constraint-level column statistics added for the optimizer in some configurations
• Oracle 23ai: New constraint-related features for JSON Relational Duality Views; constraints on JSON columns appear in DBA_CONSTRAINTS with CONSTRAINT_TYPE = ‘C’