dba_constraints: Query All Oracle Table Constraints

dba_constraints: Query All Oracle Table Constraints

Purpose

A DDL export shows every constraint as text, and a design tool shows them as a diagram. Neither lets you filter, sort or count. DBA_CONSTRAINTS does. It holds one row per constraint, so a single query can list the constraints on one table, show which are switched off, and show when each one last changed state.

DBA_CONSTRAINTS describes all constraint definitions in the database. ALL_CONSTRAINTS describes only the constraints on tables that the current user can access. Use the DBA_ view when you have the privilege and need the full picture. Use the ALL_ view when you do not.

A DBA runs this query before a data load, before a schema change, and when an application reports a constraint error. The script below takes an owner and a table name. It prints the type, name, status and last change time of each constraint on that table. A second query then adds the columns that each constraint covers. To list constraints for a whole user instead of one table, see the per-user version of this query.

Code

 1col type format a10
 2col cons_name format a30
 3select decode(constraint_type,
 4  'C', 'Check',
 5  'O', 'R/O View',
 6  'P', 'Primary',
 7  'R', 'Foreign',
 8  'U', 'Unique',
 9  'V', 'Check view') type
10, constraint_name cons_name
11, status
12, last_change
13from dba_constraints
14where owner like '&owner'
15and table_name like '&table_name'
16order by 1
17/

To see the columns of each constraint, join to DBA_CONS_COLUMNS:

 1select c.constraint_name
 2, c.constraint_type
 3, cc.position
 4, cc.column_name
 5from dba_constraints c
 6join dba_cons_columns cc
 7  on cc.owner = c.owner
 8 and cc.constraint_name = c.constraint_name
 9where c.owner like '&owner'
10and c.table_name like '&table_name'
11order by c.constraint_name, cc.position
12/

To find constraints that are not fully enforced, filter on status and validation:

1select owner, table_name, constraint_name, status, validated
2from dba_constraints
3where owner like '&owner'
4and (status = 'DISABLED' or validated = 'NOT VALIDATED')
5order by owner, table_name, constraint_name
6/

Code Breakdown

The first query

  • col type format a10 and col cons_name format a30 set the display width of two output columns. They are SQL*Plus formatting commands, not SQL.
  • decode(constraint_type, ...) turns the one-letter type code into a word. The codes C, O, P, R, U and V are mapped. The result is named type.
  • constraint_name cons_name shows the name of the constraint.
  • status shows whether the constraint is ENABLED or DISABLED.
  • last_change shows when the constraint was last enabled or disabled. It does not record other edits.
  • from dba_constraints reads the data dictionary view that holds all constraint definitions.
  • owner like '&owner' and table_name like '&table_name' use substitution variables. You type a value for each when the script runs. Because the filter uses LIKE, you can enter a pattern as well as an exact name.
  • order by 1 sorts by the first column in the select list, which is the decoded type.

The second query

  • DBA_CONS_COLUMNS has one row for each column named in a constraint. The join uses owner and constraint_name, so a constraint in one schema is never matched to a same-named constraint in another.
  • position gives the original position of the column in the definition. Sorting by it puts the columns of a multi-column key in their defined order.
  • The output has one row per column, not one row per constraint. A three-column primary key shows three rows.

The third query

  • status = 'DISABLED' finds constraints that are switched off.
  • validated = 'NOT VALIDATED' finds constraints where old rows may break the rule.

Key Points

  • The first query is a summary, not a full listing. The DECODE has no branch for the codes H, F and S, so a constraint with one of those types shows a blank type. The full list of codes is C, P, U, R, V, O, H, F and S. Hash expression is H. A constraint that involves a REF column is F. Supplemental logging is S.
  • TABLE_NAME may name a view. The column holds the name of the table or view that has the constraint. Types V and O apply to views, so a row with one of those types is not a base table constraint.
  • last_change is narrow. It holds the time the constraint was last enabled or disabled.
  • Status and validation are two checks. STATUS says whether the constraint is enforced for new data. VALIDATED says whether the old data was checked. An enabled constraint can still be NOT VALIDATED.
  • Disabled constraints stay in the dictionary. Their rows remain in the view, so this query finds them.
  • Different views, different reach. DBA_CONSTRAINTS covers the whole database. ALL_CONSTRAINTS covers only what the current user can access.

Insights and Best Practices

Read STATUS and VALIDATED together

For an enabled constraint, VALIDATED means the existing rows were checked when the constraint was turned on, and so were all later rows. NOT VALIDATED means the existing rows were not checked, but the later rows were. For a disabled constraint, NOT VALIDATED means the data may not obey the rule at all.

This is why the third query is worth saving. A constraint that shows ENABLED and NOT VALIDATED looks safe in a quick scan. It may still hold bad rows from before it was enabled. Add validated to the select list of the first query so that you see both values in one view.

Know what ENABLE NOVALIDATE does

ENABLE NOVALIDATE is the clause behind most NOT VALIDATED rows. It makes the constraint apply to new work and skips the old rows. If you find a NOT VALIDATED constraint, ask who set it and why. Plan a check of the old data, and then validate the constraint when the data is clean.

Check what disabling takes with it

Disabling a primary or unique key can remove its index. If you disable a unique or primary key constraint that is using a unique index, the database drops that index. A foreign key that points at a disabled primary or unique key cannot be enabled. When you plan to disable a key during a load, list the foreign keys that point at it first. The R_CONSTRAINT_NAME column holds the name of the unique constraint on the referenced table, and R_OWNER holds the owner of that table.

Use the other columns of the view

The view holds more than the script shows. Add the column you need to the select list:

  • SEARCH_CONDITION holds the text of a check constraint.
  • DELETE_RULE shows CASCADE, SET NULL or NO ACTION for a foreign key.
  • DEFERRABLE and DEFERRED show whether checking can wait until commit.
  • GENERATED shows GENERATED NAME for a name the system chose and USER NAME for a name a person chose.
  • INDEX_NAME is shown only for unique and primary key constraints.

System-chosen names are hard to read in an error message. Use GENERATED to find them and give new constraints clear names in your own scripts.

Keep the column listing separate

Run the column query only after the summary shows a constraint you want to inspect. If you join the two views in every run, a table with many constraints gives a long list of rows, one per column, and the summary is harder to scan.

When to Run This

  • Before a bulk load, to see which constraints are enabled and which are not.
  • After a load that used ENABLE NOVALIDATE, to find constraints that are still NOT VALIDATED.
  • When an application reports a constraint error and you need the full set of rules on the table.
  • Before you drop or rename a column, to see which constraints use it.
  • Before you disable a primary or unique key, to find foreign keys that depend on it.
  • During a schema review, to compare the constraints of a table in two environments.

Troubleshooting Common Issues

The query returns no rows. Check the owner and the table name first. Both filters use LIKE, so a typing error returns nothing and gives no error. Run the query with % for the table name to list every table for the owner, then narrow it down.

You cannot query DBA_CONSTRAINTS. Switch to ALL_CONSTRAINTS, which has the same columns. It describes only constraints on tables that the current user can access, so expect fewer rows. Ask your DBA for access if you need the full view.

The type column is blank. The DECODE has no branch for that type code. Select constraint_type as a plain column next to the decoded value. The codes H, F and S are the ones the script leaves out.

A check constraint shows no condition. The first query does not select search_condition. Add it to the select list to see the condition text. The column returns the correct value only when the row originates from the current container.

A foreign key will not enable. Look at the referenced key. Use R_OWNER and R_CONSTRAINT_NAME to find it, then check its status. A foreign key cannot be enabled while the primary or unique key it references is disabled.

A column query shows too many rows. One row appears per column in each constraint. Group by constraint_name if you want one row per constraint, or filter to a single constraint name.

References

Posts in this series