Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

10.4 Table Constraints

Four different
types of
constraints can be created on a table:

Check Primary key Unique Foreign key
These types are different enough that, with the exception of the
Primary key and Unique constraints, you need a slightly different
query for each to properly see the definitions.

10.4.1 Check Constraints

A check
constraint
is an expression that must be true
for each row in a table. This is the simplest of the constraint types
when it comes to querying the data dictionary tables. The check
expression is stored in the search_condition
column of the all_constraints table. The query in
Example 10-3 will get you the definition of all check
constraints on a particular table.

Example 10-3. A query to list check constraints on a table

COLUMN constraint_name FORMAT A20
COLUMN status FORMAT A8
COLUMN search_condition FORMAT A50 WORD_WRAPPED
SELECT constraint_name, status, search_condition
FROM all_constraints
WHERE owner = UPPER('&owner')
AND table_name = UPPER('&table_name')
AND constraint_type ='C';

The simple query in Example 10-3 is a matter of
finding all constraints of type C for the
specified table. You don't even have to join any
tables. With the other constraint types, the query gets more complex.
Following is an example run to see what check constraints exist on
the employee table:

SQL> @ex10-3
Enter value for owner: gennick
old 3: WHERE owner = UPPER('&owner')
new 3: WHERE owner = UPPER('gennick')
Enter value for table_name: employee
old 4: AND table_name = UPPER('&table_name')
new 4: AND table_name = UPPER('employee')
CONSTRAINT_NAME STATUS SEARCH_CONDITION
-------------------- -------- ------------------------------------------
SILLY_CHECK DISABLED employee_id = employee_id The status column from
all_constraints indicates whether a constraint is
active. A status of ENABLED means that a constraint is actively being
enforced. A status of DISABLED means that the DBA has disabled the
constraint, possibly to make it easier to perform maintenance. The
SILLY_CHECK constraint is indeed silly.
It's probably good that it's
disabled.

The search_condition column is of type LONG. To see check constraint definitions more than 80 bytes in length, you'll need to issue a SET LONG command. Just pick a really high value, such as SET LONG 2000.

10.4.2 Primary Key and Unique Constraints

Primary key and unique
constraints are similar in that they force
each row in a table to have a unique value in one column or
combination of columns. They are semantically different but are close
enough in structure, concept, and syntax that one query suffices for
both. When looking at constraints of these two types, you need to
include the all_cons_columns
view in your query in order to get a list of
the columns involved. Query for constraint types P
and U. Example 10-4 shows how to
do this.

Example 10-4. A query to list primary key and unique key constraints

COLUMN constraint_name FORMAT A30
COLUMN constraint_type FORMAT A1
COLUMN column_name FORMAT A30
COLUMN status FORMAT A8
SELECT ac.constraint_name, ac.constraint_type,
acc.column_name, ac.status
FROM all_constraints ac INNER JOIN all_cons_columns acc
ON ac.constraint_name = acc.constraint_name
AND ac.owner = acc.owner
WHERE ac.owner = UPPER('&owner')
AND ac.table_name = UPPER('&table_name')
AND ac.constraint_type in ('P','U')
ORDER BY ac.constraint_name, acc.position;

Ordering the columns in the constraint definition by the
position
column is done so the output matches the column order used when
originally defining a constraint. Here's an example
run:

SQL> @ex10-4
Enter value for owner: gennick
old 5: WHERE ac.owner = UPPER('&owner')
new 5: WHERE ac.owner = UPPER('gennick')
Enter value for table_name: employee
old 6: AND ac.table_name = UPPER('&table_name')
new 6: AND ac.table_name = UPPER('employee')
CONSTRAINT_NAME C COLUMN_NAME STATUS
------------------------------ - ------------------------------ --------
EMPLOYEE_PK P EMPLOYEE_ID ENABLED Oracle generally enforces unique and primary key constraints by
creating unique indexes. The column order used when creating the
indexes will match that used in defining the constraints and can
affect the performance of queries issued against the table.

10.4.3 Foreign Key Constraints

Foreign key constraints are the most complex. A foreign
key defines a list of columns in one table, called the
child table , that correlates to a primary key or
a unique constraint on a parent table . When a
row is inserted into the child table, Oracle checks to be sure that a
corresponding parent record exists. Foreign key constraints involve
two lists of columns, one in the child table on which the constraint
is defined, and another in the parent table.

The trick with foreign key constraints is to find the name of the
parent table, then find the names of the columns in the parent table
that correspond to the columns in the child table. The key to doing
this is to use the r_owner and
r_constraint_name columns in the
all_constraints view. The constraint type code for
foreign key constraints is R. A foreign key always
relates to a primary key constraint or a unique constraint on the
parent table. The name of this related constraint is in the
r_constraint_name column. Usually, the
r_owner column matches the
owner column, but
don't assume that will be the case.

To see the definition of all the foreign key constraints for a given
table, you can start with the query used for primary key constraints
and modify the WHERE clause to look only at constraint type
R. You can get rid of the constraint type columns.
Example 10-5 shows the resulting query.

Example 10-5. A script to list foreign key columns

COLUMN constraint_name FORMAT A30
COLUMN column_name FORMAT A15
SELECT ac.constraint_name, acc.column_name
FROM all_constraints ac INNER JOIN all_cons_columns acc
ON ac.constraint_name = acc.constraint_name
AND ac.owner = acc.owner
WHERE ac.owner = UPPER('&owner')
AND ac.table_name = UPPER('&table_name')
AND ac.constraint_type = 'R'
ORDER BY ac.constraint_name, acc.position;

This query will give you constraint names and a list of column names.
The following are the results showing foreign key constraints defined
on project_hours:

SQL> @ex10-5
Enter value for owner: gennick
old 5: WHERE ac.owner = UPPER('&owner')
new 5: WHERE ac.owner = UPPER('gennick')
Enter value for table_name: project_hours
old 6: AND ac.table_name = UPPER('&table_name')
new 6: AND ac.table_name = UPPER('project_hours')
CONSTRAINT_NAME COLUMN_NAME
------------------------------ ---------------
PROJ_HOURS_FKTO_EMPLOYEE EMPLOYEE_ID
PROJ_HOURS_FKTO_PROJECT PROJECT_ID Two foreign key constraints each involve one column that relates to a
second parent table. However, the query as it stands
won't tell you the name of those parent tables, nor
the names of the corresponding columns in those tables. For those,
you need to join all_constraints to itself via the
r_constraint_name and r_owner
columns. This will give you access to the parent
table's name. Example 10-6 shows
this version of the query.

Example 10-6. A script to list foreign key constraints with their target tables

COLUMN constraint_name FORMAT A30
COLUMN column_name FORMAT A15
COLUMN owner FORMAT A10
COLUMN table_name FORMAT A15
SELECT ac.constraint_name, acc.column_name,
r_ac.owner, r_ac.table_name
FROM all_constraints ac INNER JOIN all_cons_columns acc
ON ac.constraint_name = acc.constraint_name
AND ac.owner = acc.owner
INNER JOIN all_constraints r_ac
ON ac.r_owner = r_ac.owner
AND ac.r_constraint_name = r_ac.constraint_name
WHERE ac.owner = UPPER('&owner')
AND ac.table_name = UPPER('&table_name')
AND ac.constraint_type = 'R'
ORDER BY ac.constraint_name, acc.position;

The following are the results from a run of Example 10-6:

SQL> @ex10-6
Enter value for owner: gennick
old 9: WHERE ac.owner = UPPER('&owner')
new 9: WHERE ac.owner = UPPER('gennick')
Enter value for table_name: project_hours
old 10: AND ac.table_name = UPPER('&table_name')
new 10: AND ac.table_name = UPPER('project_hours')
CONSTRAINT_NAME COLUMN_NAME OWNER TABLE_NAME
------------------------------ --------------- ---------- ---------------
PROJ_HOURS_FKTO_EMPLOYEE EMPLOYEE_ID GENNICK EMPLOYEE
PROJ_HOURS_FKTO_PROJECT PROJECT_ID GENNICK PROJECT Because most foreign key constraints relate to the parent
table's primary key, you may want to stop here.
However, as it is possible to relate a foreign key to a unique key on
the parent table, you may want see the corresponding list of parent
table columns to understand the constraint. To do this, you must join
with all_cons_columns once
again and pick up the columns that go with the related parent table
constraint.

This final join to all_cons_columns can cause quite a performance hit, enough for you to notice. For that reason, you may prefer to remain with the query version in Example 10-6, and simply infer the parent column names based on the foreign key column names that Example 10-6 provides.

Example 10-7 shows a final version of the query to
list foreign key constraints. This version joins a second time to
all_cons_columns to display the foreign key
columns side by side with their respective parent table columns.

Example 10-7. A script to list foreign key constraints together with their target columns

COLUMN constraint_name FORMAT A30
COLUMN column_name FORMAT A15
COLUMN target_column FORMAT A20
SELECT ac.constraint_name, acc.column_name,
r_ac.owner || '.' || r_ac.table_name
|| '.' || r_acc.column_name target_column
FROM all_constraints ac INNER JOIN all_cons_columns acc
ON ac.constraint_name = acc.constraint_name
AND ac.owner = acc.owner
INNER JOIN all_constraints r_ac
ON ac.r_owner = r_ac.owner
AND ac.r_constraint_name = r_ac.constraint_name
INNER JOIN all_cons_columns r_acc
ON r_ac.owner = r_acc.owner
AND r_ac.constraint_name = r_acc.constraint_name
AND acc.position = r_acc.position
WHERE ac.owner = UPPER('&owner')
AND ac.table_name = UPPER('&table_name')
AND ac.constraint_type = 'R'
ORDER BY ac.constraint_name, acc.position;

The all_cons_columns table's
position column forms part of the join criteria.
This ensures that matching columns are output together, on the same
line. The results from the script are as follows:

SQL> @ex10-7
Enter value for owner: gennick
old 14: WHERE ac.owner = UPPER('&owner')
new 14: WHERE ac.owner = UPPER('gennick')
Enter value for table_name: project_hours
old 15: AND ac.table_name = UPPER('&table_name')
new 15: AND ac.table_name = UPPER('project_hours')
CONSTRAINT_NAME COLUMN_NAME TARGET_COLUMN
------------------------------ --------------- ------------------------------
PROJ_HOURS_FKTO_EMPLOYEE EMPLOYEE_ID GENNICK.EMPLOYEE.EMPLOYEE_ID
PROJ_HOURS_FKTO_PROJECT PROJECT_ID GENNICK.PROJECT.PROJECT_ID To conserve horizontal space, Example 10-7 combines
the target owner, table, and column names into a single,
period-delimited string. Thus,
GENNICK.EMPLOYEE.EMPLOYEE_ID refers to the
EMPLOYEE_ID column of the
EMPLOYEE table owned by the user
GENNICK.

The COLUMN definitions in Example 10-7 don't allow for the maximum length of constraint and column names. One of the problems you run into when querying the data dictionary tables is you often end up wanting to display more columns than will fit on your screen.