Table Constraints

2017-11-03 09:05:07

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.

Категории