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 (USER_CONSTRAINTS) and ALL_CONS_COLUMNS (or USER_CONS_COLUMNS). In this post, I look at some queries I like to use that take advantage of these views from the Oracle Data Dictionary.
The ALL_CONSTRAINTS view is great for finding basic constraint details. The next SQL*Plus snippet demonstrates this in use.
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';
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.