When I am working with an Oracle database, I still find myself using SQL*Plus for many quick and dirty database queries. In particular, I often look up constraints in SQL*Plus. In this post, I look at the Oracle database views and queries that I use most to get an idea what constraints I am dealing with.
I have found the two most important views for determining basic database constraints are ALL_CONSTRAINTS [1] (USER_CONSTRAINTS [2]) and ALL_CONS_COLUMNS [3] (or USER_CONS_COLUMNS [4]). In this post, I look at some queries I like to use that take advantage of these views from the Oracle Data Dictionary [5].
The ALL_CONSTRAINTS view is great for finding basic constraint details. The next SQL*Plus snippet demonstrates this in use.
displayConstraintInfo.sql
set linesize 180
set verify off
accept constraintName prompt "Constraint Name: "
SELECT constraint_name, constraint_type, r_constraint_name, table_name,
search_condition
FROM all_constraints
WHERE constraint_name = '&constraintName';
The above snippet will prompt for a constraint name and then provide some fundamental characteristics of that constraint provided by the ALL_CONSTRAINTS view. One of these characteristics is CONSTRAINT_TYPE, which is one of the following values: 'C' (Check Constraint), 'P' (Primary Key), 'R' (Referential/Foreign Key), 'U' (Unique), 'V' (with check option on a view), 'O' (with read only on a view). The above query requires one to know the constraint name. The next query will show similar information for constraints on a given table.
set linesize 180
set verify off
accept tableName prompt "Table Name: "
SELECT constraint_name, constraint_type, r_constraint_name, table_name,
search_condition
FROM all_constraints
WHERE table_name = '&tableName';
The above query provides the constraints on a given table, but it is often useful to know which columns in particular on the table have constraints. This is easily done by joining the ALL_CONS_COLUMNS view to the ALL_CONSTRAINTS view.
displayConstraintsOnTableColumns.sql
set linesize 180
set verify off
accept tableName prompt "Table Name: "
SELECT c.constraint_name, c.constraint_type, c.r_constraint_name,
c.table_name, cc.column_name, cc.position, c.search_condition
FROM all_constraints c, all_cons_columns cc
WHERE c.table_name = '&tableName'
AND c.constraint_name = cc.constraint_name;
Another useful query using these two constraints-related views is one that provides information on referential integrity constraints (CONSTRAINT_TYPE of R). In particular, this simple query shows the constraints for a given table that are foreign key constraints and which primary key constraints they depend on.
set linesize 180
set verify off
accept tableName prompt "Table Name: "
SELECT cf.constraint_name "FOREIGN KEY",
cp.constraint_name "DEPENDS ON",
cp.table_name, ccp.column_name, ccp.position
FROM all_constraints cp, all_cons_columns ccp, all_constraints cf
WHERE cp.table_name = '&tableName'
AND cp.constraint_name = ccp.constraint_name
AND cf.r_constraint_name = cp.constraint_name
AND cf.r_constraint_name = ccp.constraint_name;
In this post I've summarized some of the useful queries one can construct from the Oracle Data Dictionary views ALL_CONSTRAINTS and ALL_USER_CONS_COLUMNS.
Original posting available at http://marxsoftware.blogspot.com/ [6] (Inspired by Actual Events)
