ASA Getting Started
  Updating the Database
    Integrity checking

Errors on DELETE or UPDATE

Foreign key errors can also arise when doing update or delete operations. For example, suppose you try to remove the R&D department from the department table. The dept_id field, being the primary key of the department table, constitutes the ONE side of a one-to-many relationship (the dept_id field of the employee table is the corresponding foreign key, and hence forms the MANY side). A record on the one side of a relationship may not be deleted until all corresponding records on the MANY side are deleted.

DELETE
FROM department
WHERE dept_id = 100

Example: DELETE errors 

An error is reported indicating that there are other records in the database that reference the R&D department, and the delete operation is not carried out.

primary key for row in table 'department' is referenced in another table

In order to remove the R&D department, you need to first get rid of all employees in that department:

DELETE
FROM employee
WHERE dept_id = 100

You can now perform the deletion of the R&D department.

You should cancel these changes to the database (for future use) by entering a ROLLBACK statement:

ROLLBACK WORK

All changes made since the last successful COMMIT WORK will be undone. If you have not done a COMMIT, then all changes since you started Interactive SQL will be undone.

Example: UPDATE errors 

The same error message is generated if you perform an update operation that makes the database inconsistent.

For example, the following UPDATE statement causes an integrity error:

UPDATE department
SET dept_id = 600
WHERE dept_id = 100

In all of the above examples, the integrity of the database was checked as each command was executed. Any operation that would result in an inconsistent database is not performed.

Example: checking the integrity after the COMMIT WORK is complete 

It is possible to configure the database so that the integrity is not checked until the COMMIT WORK is done. This is important if you want to change the value of a referenced primary key; for example, changing the R&D department's ID from 100 to 600 in the department and employee tables. In order to make these changes, the database has to be inconsistent in between the changes. In this case, you must configure the database to check only on commits.

For more information, see WAIT_FOR_COMMIT option [database].

You can also define foreign keys in such a way that they are automatically fixed. In the above example, if the foreign key from employee to department were defined with ON UPDATE CASCADE, then updating the department ID would automatically update the employee table.

In the above cases, there is no way to have an inconsistent database committed as permanent. Adaptive Server Anywhere also supports alternative actions if changes would render the database inconsistent.

For more information, see the chapter Ensuring Data Integrity.