User's Guide
PART 2. Relational Database Concepts
CHAPTER 14. Ensuring Data Integrity
The relational structure of the database enables information within the database to be identified by the personal server, and ensures that relationships between tables, described in the database structure, are properly upheld by all the rows in each table.
Enforcing entity integrityWhen a row is inserted or is updated, the database server ensures that the primary key for the table is still valid: that each row in the table is uniquely identified by the primary key.
The employee table in the sample database uses an employee ID as the primary key. When a new employee is added to the table, the database server checks that the new employee ID value is unique and is not NULL.
The sales_order_items table in the sample database uses two columns to define a primary key.
This table holds information about items ordered. One column contains an id specifying an order, but there may be several items on each order, so this column by itself cannot be a primary key. An additional line_id column identifies which line corresponds to the item. The columns id and line_id, taken together, specify an item uniquely, and form the primary key.
If a client application breaches entity integrityEntity integrity requires that each value of a primary key be unique within the table, and that there are no NULL values. If a client application attempts to insert or update a primary key value, and provides values that are not unique, entity integrity would be breached.
If an attempt to breach entity integrity is detected, the new information is not added to the database. Instead, the client application receives an error.
It is up to the application programmer to decide how to present this information to the user and enable the user to take appropriate action. The appropriate action is usually just to provide a different, unique, value for the primary key.
Primary keys enforce entity integrityOnce the primary key for each table is specified, no further action is needed by client application developers or by the database administrator to maintain entity integrity.
The primary key for a table is defined by the table owner when the table is created. If the structure of a table is modified at a later date, the primary key may also be redefined.
Some application development systems and database design tools allow you to create and alter database tables. If you are using such a system, you may not have to enter the CREATE TABLE or ALTER TABLE command explicitly: the application generates the statement itself from the information you provide.
For information on creating primary keys, see Creating primary and foreign keys. For the detailed syntax of the CREATE TABLE statement, see CREATE TABLE statement. For information about changing table structure, see the ALTER TABLE statement.
Enforcing referential integrityA foreign key relates the information in one table (the foreign table) to information in another (referenced or primary) table. A particular column, or combination of columns, in a foreign table is designated as a foreign key to the primary table.
For the foreign key relationship to be valid, the entries in the foreign key must correspond to the primary key values of a row in the referenced table. Occasionally, some other unique column combination may be referenced, instead of a primary key.
The sample database contains an employee table and a department table. The primary key for the employee table is the employee ID, and the primary key for the department table is the department ID.
One of the items of information about each employee is the department ID of the department to which they belong. In the employee table, the department ID is called a foreign key for the department table; each department ID in the employee table corresponds exactly to a department ID in the department table.
The foreign key relationship is a many-to-one relationship. Several entries in the employee table have the same department ID entry, but the department ID is the primary key for the department table, and so is unique. If a foreign key were able to reference a column in the department table containing duplicate entries, there would be no way of knowing which of the rows in the department table is the appropriate reference.
Suppose the database also contained an office table, listing office locations. The employee table might have a foreign key for the office table that indicates where the employee's office is located. The database designer may wish to allow for an office location not being assigned at the time the employee is hired. In this case, the foreign key is optional and should allow the NULL value to indicate that it is optional when the office location is unknown or when the employee does not work out of an office. A foreign key that is not optional is called mandatory.
Foreign keys enforce referential integrityLike primary keys, foreign keys are created using the CREATE TABLE statement or ALTER TABLE statement.
Once a foreign key has been created, the column or colums in the key can contain only values that are present as primary key values in the table associated with the foreign key.
For information on creating foreign keys, see Creating primary and foreign keys.
Losing referential integrityReferential integrity can be lost in the following ways:
If a primary key value is updated or deleted, all those foreign keys referencing it would be left in an invalid state.
If a new row is added to the foreign table, and a value is entered for the foreign key that has no corresponding primary key value, the database would be left in an invalid state.
Adaptive Server Anywhere provides protection against both types of integrity loss.
If a client application breaches referential integrityIf a client application updates or deletes a primary key value in a table, and if that primary key value is referenced by a foreign key elsewhere in the database, there is a danger of a breach of referential integrity.
If the server allowed the primary key to be updated or deleted, and made no alteration to the foreign keys that referenced it, the foreign key reference would be invalid. Any attempt to use the foreign key reference, for example in a SELECT statement using a KEY JOIN clause, would fail, as no corresponding value in the referenced table would exist.
While breaches of entity integrity are generally straightforward for Adaptive Server Anywhere to handle, simply by refusing to enter the data and returning an error message, potential breaches of referential integrity are more complicated.
There are several options available to ensure that referential integrity is maintained. These options are called referential integrity actions.
Referential integrity actionsThe simplest way to maintain referential integrity when a referenced primary key is updated or deleted is to disallow the update or delete.
Often it is also possible to take an action on each foreign key to maintain referential integrity. The CREATE TABLE and ALTER TABLE statements allow database administrators and table owners to specify what action should be taken on foreign keys that reference a modified primary key.
Each of the available referential integrity actions may be specified separately for updates and deletes of the primary key:
RESTRICT Generate an error if an attempt is made to modify a referenced primary key value, and do not carry out the modification. This is the default referential integrity action.
SET NULL Set all foreign keys that reference the modified primary key to NULL.
SET DEFAULT Set all foreign keys that reference the modified primary key to the default value for that column (as specified in the table definition).
CASCADE When used with ON UPDATE, update all foreign keys that reference the updated primary key to the new value. When used with ON DELETE, delete all rows containing foreign keys that reference the deleted primary key.
Referential integrity actions are implemented using system triggers. The trigger is defined on the primary table, and is executed using the permissions of the owner of the primary table.
Referential integrity checkingFor foreign keys defined to RESTRICT operations that would violate referential integrity, checks are carried out by default at the time a statement is executed. If you specify a CHECK ON COMMIT clause, then the checks are carried out only when the transaction is committed.
The setting of the WAIT_FOR_COMMIT database option controls the behavior when a foreign key is defined to restrict operations that would violate referential integrity. This option is overridden by the CHECK ON COMMIT clause.
With the default, wait_for_commit set to OFF, an operation that would leave the database inconsistent is not allowed to execute. For example, a DELETE operation of a department that has employees in it is not allowed. The statement:
DELETE FROM department WHERE dept_id = 200
gives the error primary key for row in table 'department' is referenced in another table.
If wait_for_commit is set to ON, referential integrity is not checked until a commit is executed. If the database is in an inconsistent state, the commit is not allowed and an error is reported. In this mode, a department with employees could be deleted. However, the change could not be committed to the database until one of the following actions is taken:
The employees belonging to that department are also deleted or reassigned.
This search condition can also be used on a SELECT statement to select the rows that violate referential integrity.
The dept_id row is inserted back into the department table.
The transaction is rolled back to undo the DELETE operation.