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 a10andcol cons_name format a30set 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 codesC,O,P,R,UandVare mapped. The result is namedtype.constraint_name cons_nameshows the name of the constraint.statusshows whether the constraint isENABLEDorDISABLED.last_changeshows when the constraint was last enabled or disabled. It does not record other edits.from dba_constraintsreads the data dictionary view that holds all constraint definitions.owner like '&owner'andtable_name like '&table_name'use substitution variables. You type a value for each when the script runs. Because the filter usesLIKE, you can enter a pattern as well as an exact name.order by 1sorts by the first column in the select list, which is the decoded type.
The second query
DBA_CONS_COLUMNShas one row for each column named in a constraint. The join usesownerandconstraint_name, so a constraint in one schema is never matched to a same-named constraint in another.positiongives 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
DECODEhas no branch for the codesH,FandS, so a constraint with one of those types shows a blank type. The full list of codes isC,P,U,R,V,O,H,FandS. Hash expression isH. A constraint that involves aREFcolumn isF. Supplemental logging isS. TABLE_NAMEmay name a view. The column holds the name of the table or view that has the constraint. TypesVandOapply to views, so a row with one of those types is not a base table constraint.last_changeis narrow. It holds the time the constraint was last enabled or disabled.- Status and validation are two checks.
STATUSsays whether the constraint is enforced for new data.VALIDATEDsays whether the old data was checked. An enabled constraint can still beNOT VALIDATED. - Disabled constraints stay in the dictionary. Their rows remain in the view, so this query finds them.
- Different views, different reach.
DBA_CONSTRAINTScovers the whole database.ALL_CONSTRAINTScovers 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_CONDITIONholds the text of a check constraint.DELETE_RULEshowsCASCADE,SET NULLorNO ACTIONfor a foreign key.DEFERRABLEandDEFERREDshow whether checking can wait until commit.GENERATEDshowsGENERATED NAMEfor a name the system chose andUSER NAMEfor a name a person chose.INDEX_NAMEis 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 stillNOT 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
- Oracle Database Reference 19c - ALL_CONSTRAINTS - Column definitions for the constraint views, including CONSTRAINT_TYPE codes, STATUS, VALIDATED, INDEX_NAME and LAST_CHANGE
- Oracle Database Reference 19c - DBA_CONSTRAINTS - The database-wide view, with the same columns as ALL_CONSTRAINTS
- Oracle Database Reference 19c - ALL_CONS_COLUMNS - The view that maps each constraint to its columns and the position of each column
- Oracle Database SQL Language Reference 19c - constraint - The ENABLE, DISABLE, VALIDATE and NOVALIDATE clauses and the rules for foreign keys and deferrable constraints
- ORACLE-BASE - Constraint Checking Updates - A community walkthrough of ENABLE NOVALIDATE and the validation states of a constraint
Posts in this series
- Oracle: List Tables Using a Table as a Foreign Key
- dba_constraints: Query All Oracle Table Constraints
- Oracle: Enable Disabled Foreign Key Constraints
- Oracle Disabled Foreign Key Constraints: dba_constraints
- ORA-02298: Find Missing Foreign Key Values in Oracle
- Oracle: Find Parent Tables for Repair Using dba_constraints
- Oracle User Table Constraints Query with dba_constraints