HP 3000 Manuals HP 3000 Manuals
Designing Integrity Constraints You can enforce integrity in particular tables or in the relationships among tables in your design by creating integrity constraints on specific columns. Unique, referential, and check integrity constraints can be included as part of the CREATE TABLE or ALTER TABLE statements. A check constraint can be included as part of a CREATE VIEW statement. A unique constraint lets you eliminate duplicate key values. To create the unique constraint, you use the PRIMARY KEY or UNIQUE option along with the NOT NULL clause in the CREATE TABLE or ALTER TABLE statement. To enforce uniqueness, ALLBASE/SQL automatically creates a unique index on the specified key in the same DBEFileSet as the table. A unique constraint differs from a unique index in that you do not create it with a separate SQL statement, and you do not have to name it. A referential constraint lets you enforce a relationship of dependency between different keys. This relationship implies the existence of both a referenced table and a referencing table, which may be the same table. The referential relationship means that a key value must exist in the referenced table before a row containing that key value is inserted in the referencing table. (However, the foreign key in the referencing table can contain NULL values, even though the primary or unique key in the referenced table cannot contain NULL values.) The referenced table must be created with a PRIMARY KEY or UNIQUE clause in the CREATE TABLE or ALTER TABLE statement. The primary or unique key is then referred to in the FOREIGN KEY or REFERENCES clause in the CREATE TABLE or ALTER TABLE statement for the referencing table. Table check constraints additionally ensure that a specified search condition does not evaluate to false for any row of a table. The search condition may evaluate to unknown if a column specified in the condition contains a NULL value. A search condition is defined on columns of a table in the CREATE TABLE or ALTER TABLE statement. A check constraint for a view is defined through the WITH CHECK OPTION clause. This constraint ensures that no changes made through the view violate its definition. Such a check constraint is enforced during an insert or update to a table through the view. The check must satisfy first the view constraints and the constraints defined on the table on which the view is based. You can also defer constraint checking to the transaction level and set general error checking to row level. Constraint error checking is performed at the current general error checking level unless constraint checking is deferred. Deferring constraint error checking avoids constraint errors that will be resolved by the end of the transaction. Setting general error checking to row level avoids some logging and rollback overhead if you are using nonarchive logging. For complete information about integrity constraints, refer to "Constraints, Procedures, and Rules" chapter in the ALLBASE/SQL Reference Manual, and to the application programming guide for the language of your choice.