Oracle SQL*Plus: The Definitive Guide, 2nd Edition

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.

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