User's Guide Part III. Using SQL Anywhere Chapter 18. Ensuring Data IntegrityThe relational structure of the database enables information within the database to be identified by the database engine, and ensures that relationships described in the database structure between tables are properly upheld by all the rows in each table.
When a new row in a table is created, or when a row is updated, SQL Anywhere 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, SQL Anywhere 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 columns identifies which line corresponding to the item. The two columns id and line_id, taken together, specify an item uniquely, and form the primary key.
Entity 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 SQL Anywhere detects an attempt to breach entity integrity, it does not add the new information to the database, and instead reports an error to the client application.
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 in this case is usually just to provide a unique value for the primary key.
Once 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".
A 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.
The entries in the foreign key must correspond to the primary key values of a row in the referenced table for the foreign key relationship to be valid. (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.
Like primary keys, foreign keys are created using the CREATE TABLE statement or ALTER TABLE statement.
Once a foreign key has been created, SQL Anywhere ensures that the columns 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".
Referential integrity can be lost in the following ways:
SQL Anywhere provides protection against both types of integrity loss.
If 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 database engine 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 SQL Anywhere to handle, simply by refusing to enter the data and returning an error message, these potential breaches of referential integrity are more complicated.
SQL Anywhere ensures that referential integrity is maintained, and provides four options for how this is to be done. These options are called referential integrity actions.
The simplest way of maintaining referential integrity when a referenced primary key is updated or deleted is to disallow the update or delete.
It is often 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:
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.
For foreign keys defined to RESTRICT operations that would violate referential integrity, checks can be carried out at the time a statement is executed (the default) or only when a transaction is committed, by specifying the CHECK ON COMMIT clause.
SQL Anywhere operates in two different modes when a foreign key is defined to RESTRICT operations that would violate referential integrity, depending on the setting of the wait_for_commit database option. This option is overridden by the CHECK ON COMMIT clause.
With the default setting (wait_for_commit = off --- see "SET OPTION statement") a database 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 departmentWHERE 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: