## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

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

## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

No credit card required