Constraints
When you create or alter a table, define constraints for the table. Constraints are used to check for appropriate data values whenever data is entered or updated in the table.
Constraints are checked at the end of every statement that modifies the table. If the constraint is violated, the DBMS returns an error and aborts the statement. If the statement is in a multi-statement transaction, the transaction is not aborted.
Note: For other mechanisms used to ensure the integrity of data in a table, including integrities and rules, see the Security Guide.
Define constraints using the CREATE TABLE or ALTER TABLE statements. In Director, use the New Table page. In VDBA, use the Create Table or Alter Table dialog.
Constraint Types
There are several types of constraints:
• Unique
• Check
• Referential
Unique Constraints
You can define unique constraints at both the column and the table level. Columns that you specify as unique or that you use as part of a table-level unique constraint cannot be nullable. Column-level unique constraints ensure that no two rows in the table can have the same value for that column. At the table level, you can specify several columns, all of which are taken together to determine uniqueness.
For example, if you specify the department number and department location columns to be unique at the table level, no two departments in the same location can have the same name:
CREATE TABLE depts ( dname CHAR(10) NOT NULL,
dlocation CHAR(10) NOT NULL)
CONSTRAINT unique_dept UNIQUE (dname, dlocation));
The columns are declared NOT NULL, as required by the unique constraint. You can specify a maximum of 32 columns in a table-level unique constraint; however, a table can have any number of unique constraints.
In contrast, specifying the department name and department location columns to be unique at the column level is more restrictive—in this case, no two departments can have the same name, regardless of the location, and no two locations can have the same name either.
CREATE TABLE dept (dname CHAR(10) UNIQUE NOT NULL,
dlocation CHAR(10) UNIQUE NOT NULL);
In Director or VDBA define column-level unique constraints by enabling the Unique check box for the column. In VDBA, define table-level unique constraints using the Table Level Unique Constraint dialog.
Check Constraints
Check constraints ensure that the contents of a column fulfills user-specified criteria. In SQL, use the CHECK option.
For example, to ensure that salaries are positive numbers, you could create the following constraint:
create table emps (name char(25), sal money,
constraint check_salary check (sal > 0));
In VDBA, use the Table Level Check Constraint dialog. Enter a Boolean expression involving one or more columns. For example, enter the following expression to ensure that only positive values are accepted in the salary column:
salary > 0
The following example ensures that only positive values are accepted in the budget column and that expenses do not exceed the budget:
budget > 0 and expenses <= budget
Referential Constraints
Referential constraints are used to validate an entry against the contents of a column in another table (or another column in the same table), allowing you to maintain the referential integrity of your tables. In SQL, use the REFERENCES option of the CREATE TABLE and ALTER TABLE statements.
For information on referential action options, see the SQL Reference Guide.
When defining a referential constraint, you must consider the following points:
• The table that you intend to reference must exist, with the appropriate primary key or unique constraint defined.
• Referencing columns from the table in which the constraints are being defined are compared to columns that make up the primary key or a table-level unique constraint in the referenced, or parent, table.
• The data types of the columns must be comparable, and the referencing columns must correspond in number and position to those in the referenced table.
• You must have references permission for the referenced columns.
Example: Define a Referential Constraint
The following example of a referential constraint assumes that the employee table exists with a primary key constraint defined involving a name and an employee number column.
This example verifies the contents of the name and empno columns in the manager table against the primary key columns in the employee table, to ensure that anyone entered into the table of managers is on file as an employee.
CREATE TABLE manager (name CHAR(10),
empno CHAR(5),
...
FOREIGN KEY (name, empno) REFERENCES emp);
Primary Key Constraint
Primary key constraints can be used to denote one or more columns, which other tables reference in referential constraints.
Note: Primary key constraints can be used as an alternative and slightly more restrictive form of unique constraint, but need not be used at all.
To define a primary key, you choose which columns are to be part of the key and assign to each a particular position in the key. Columns that are part of the primary key cannot be nullable, and the primary key is implicitly unique. A table can have only one primary key, which can consist of a maximum of 32 columns.
To create a Primary Key constraint, use the SQL CREATE TABLE or ALTER TABLE statements.
Example: Define a Primary Key Constraint
For example, using SQL, in the partnumbers table, define the partno column as the primary key. The inventory table has a comparable column named ipartno. A referential constraint can be defined on the inventory table based on the partnumbers table.
CREATE TABLE partnumbers
(partno INTO NOT NULL PRIMARY KEY,
desc VARCHAR(20),
type CHAR(1) NOT NULL);
CREATE TABLE inventory
(ipartno INT NOT NULL,
start_date DATE,
type CHAR(1) NOT NULL,
end_date DATE,
qty INT NOT NULL);
ALTER TABLE inventory ADD CONSTRAINT fk_ipart FOREIGN KEY (ipartno) REFERENCES partnumbers;
In this case, the part numbers in the inventory table are checked against those in the partnumbers table. When defining this referential constraint, it is not necessary to specify the column to be referenced in the partnumbers table because it was defined as the primary key.
Indexes for Constraints
Special indexes are created whenever you specify a unique, primary key, or referential constraint for a table. No user—including the table owner—can explicitly drop these system-generated constraint indexes, because they are used internally to enforce the constraints.
For primary key and unique constraints, the index is built on the constrained columns as a mechanism to detect duplicates as rows are added or updated.
For referential constraints, the index is built on the referencing columns of the constraint. This index ensures that the internal procedures that enforce the constraint when a referenced row is deleted or referenced columns are updated can be executed efficiently. When a referencing row is inserted or referencing columns are updated, the unique constraint index built on the referenced columns is used to ensure the efficiency of enforcing the constraint.
Note: If you create an index, and then create a constraint that uses the index, the index cannot be dropped (but the constraint can be dropped). If you create a constraint using the WITH INDEX=name clause but do not create the index (which causes the system to generate the named index), and you drop the constraint, the index is also dropped, because the index is a system index and not a user index.
Options for Constraint Indexes
You can specify options for constraint indexes by using the constraint WITH clause on CREATE TABLE.
In VDBA, the Table Level Unique Constraint dialog (accessible from both the Create Table and the Alter Table dialogs) has an Index button that allows you to fine tune the index used to enforce unique constraints. For additional information about the various dialog options, see online help.
These options give you more control over the index that is created, including the ability to specify:
• The location of the index
Constraint indexes are, by default, stored in the default location for data files. Because of storage space or concurrency considerations, they can be stored in an alternate location.
• The storage structure type and other structure-specific characteristics
By default, a B-tree storage structure is used for constraint indexes, but in some cases, a different structure can be more efficient. For more information on storage structures, see the chapter “Choosing Storage Structures and Secondary Indexes.”
• That an existing secondary index be used instead of generating a new index
You can save the overhead of generating an unnecessary index if you have an appropriate secondary index available. Simply indicate the name of the secondary index, and it is used to enforce the constraint instead of generating a new one.
To use an existing secondary index for referential constraints, the referencing columns must match the first n columns of the index (although not necessarily in order).
To use an existing secondary index for unique or primary key constraints, the index must be defined on exactly the same columns as the constraint, it must be a unique index, and it must specify that uniqueness is checked only after the UPDATE statement is completed.
Note: Indexes enforcing uniqueness constraints in the ANSI/ISO style, as required by referenced columns of a referential constraint, must specify the UNIQUE_SCOPE = STATEMENT option in the corresponding CREATE INDEX statement.
For more information on creating a secondary index and specifying the scope for uniqueness checking, see the chapter “Choosing Storage Structures and Secondary Indexes” and online help.
• In the case of referential constraints, that no index be generated
The index built for referential constraints is used only to improve the efficiency of the internal procedures that enforce the constraint when a referenced row is deleted or referenced columns are updated. Because the procedures can execute in its absence, the index is optional.
In the absence of a referential constraint index, the internal procedures use a secondary index, if one is available that is defined on the referencing columns, and so the efficiency of the procedures can not be compromised. However, if no such secondary index exists, a full-table scan of the referencing table is necessary. Thus, choosing not to generate a referential constraint must be reserved for special circumstances, such as when:
– An appropriate secondary index is available
– Very few rows are in the referencing table (as in a prototype application)
– Deletes and updates are rarely (if ever) performed on the referenced table
• That the base table structure be used for constraint enforcement
This option requires a table that uses a keyed storage structure. Because heap, which is a non-keyed structure, is the default when you create a table, this option cannot be specified for constraints added at that time. The ability to use the base table structure for constraint enforcement is available only for constraints that are added when altering an existing table.
Before you can specify the base table structure in lieu of a constraint index, you need to modify the table to change its storage structure type and to specify key columns to match the constraint columns it is used to enforce. If the base table structure is being used to enforce a primary key or unique constraint, you must also specify that uniqueness is checked only after the update statement is completed.
Note: Indexes enforcing uniqueness constraints in the ANSI/ISO style, as required by referenced columns of a referential constraint, must specify the UNIQUE_SCOPE = STATEMENT option in the corresponding CREATE INDEX statement.
For more information on modifying storage structure of a table, specifying key values, and specifying the scope for uniqueness checking, see the chapter “Maintaining Storage Structures.”
Delete Constraints
To delete a constraint using SQL, use the ALTER TABLE...DROP CONSTRAINT statement. When you drop a constraint, you must specify RESTRICT or CASCADE. RESTRICT aborts the operation if there are any constraints that depend on the constraint being dropped. CASCADE deletes all dependent constraints.
In VDBA, the Create Table dialog allows you to delete any constraint as you are designing your table, without restrictions. After you have saved the table, remove constraints using the Alter Table dialog.
Because removing constraints is more complicated when altering a table, each dialog in VDBA that allows you to work with constraints gives you two delete options when altering a table. These same dialogs give you only one delete option when creating a table:
• Delete performs a restrictive delete, assuming that there are no dependent constraints. If you delete a constraint using this button, the constraint is dropped only if there are no dependent constraints; otherwise, if there are dependent constraints, the delete operation results in an error.
• Del Cascade performs a cascading delete by also dropping all dependent constraints. This is not available—and not needed—when creating a table.
For example, a unique constraint in one table can have a dependent referential constraint in another table. In this case, if you altered the table in which the unique constraint was defined and attempted to perform a Delete operation in the Table Level Unique Constraint dialog, it results in an error due to the existence of the dependent referential constraint. To delete the unique constraint successfully, use Del Cascade, which also deletes the referential constraint in the other table.
Note: In VDBA, column-level unique constraints are defined directly in the Create Table or Alter Table dialog. You cannot, however, remove a column-level unique constraint simply by disabling its Unique check box in the Alter Table dialog. To remove a column-level unique constraint, you must use the Table Level Unique Constraint dialog.