Check Constraints
You can create a check constraint as part of the table definition when you create a table. If a table already exists, you can add a CHECK constraint. Tables and columns can contain multiple check constraints. If a check constraint already exists, you can modify or delete it. For example, you may want to modify the expression that is used by the check constraint on a column in the table.
The following restrictions are actual for check constraints:
- The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other tables.
- Conditions of check constraints cannot contain the following constructs:
- Subqueries and scalar subquery expressions
- Calls to the functions that are not deterministic
- Calls to user-defined functions
- Dereferencing of REF columns
- Nested table columns or attributes
- The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
- Date constants that are not fully specified
Creating a Check Constraint
- In the Database Explorer window, right click a required table.
- Click Edit Table on the shortcut menu.
- Select the Constraints tab.
- Click New Check Constraint on the shortcut menu. The Check Constraint Properties dialog box will open.
- Enter a condition and click OK.
When the Table Editor is open, you can switch to the Constraints tab, and edit a constraint using the shortcut menu.
To delete a constraint, select a required one, and click Delete Constraint on the shortcut menu.
Example
Consider you need to ensure that all employees in table Emp have and will have salary that is more than 500. This can be implemented best with check constraint. Open the table and create the constraint as described above. In the Condition type field - Sal>500, and save the document. At this moment all data in the table is checked for validity and, if it satisfies the condition, new constraint is applied to the table.
This example assumes you do not change some of constraint’s options which are located at the bottom of constraint editor. These options add some flexibility to constraints both in the moments of creation and altering. For example, you can:
- Clear the Enabled option to prevent constraint from activating. You can enable it later.
- Clear the Validate option to avoid verification of data that table already contains. Newly added or modified data still will be checked for validity.
- Select the Deferrable option to preserve opportunity of deferring check operation until after a transaction is committed.
Want to Find out More?
Overview
All features
Request a demo
