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.
|
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.
|
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 .
|
