First Guide to SQL Anywhere Studio
   PART 3. Basic SQL
     CHAPTER 15. Updating the Database       

Validity checking

Adaptive Server Anywhere automatically checks for some common errors in your data.

  Inserting duplicate data

For example, suppose you attempt to create a department but supply a dept_id value that is already in use:

To do this, enter the command:

INSERT
INTO department ( dept_id, dept_name, dept_head_id )
VALUES ( 200, 'Eastern Sales', 902 )

The INSERT is rejected, as it would make the primary key for the table not unique.

Primary key 

A primary key is a set of columns that uniquely identifies each row in a table. For example, the dept_id column is the primary key for the department table; given a valid department ID number, there is exactly one row in the department table with that number. The primary key for the sales_order_items table is composed of the id and line_id columns, meaning that there should never be two items in the same order with the same line number.

  Inserting incorrect values

Another mistake is to type an incorrect value. The following statement inserts a new row in the sales_order table, but incorrectly supplies a sales_rep ID that does not exist in the employee table.

INSERT
INTO sales_order ( id, cust_id, order_date,
sales_rep)
VALUES ( 2700, 186, '1995-10-19', 284 )

Foreign key 

The primary key for the employee table is the employee ID number. The sales rep ID number in the sales_rep table is a foreign key for the employee table, meaning that each sales rep number in the sales_order table must match the employee ID number for some employee in the employee table.

When you try to add an order for sales rep 284 you get an error message:

No primary key value for foreign key 'ky_so_employee_id' in table 'sales_order'

There isn't an employee in the employee table with that ID number. This prevents you from inserting orders without a valid sales rep ID. This kind of validity checking is called referential integrity checking, as it maintains the integrity of references among the tables in the database.

  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.

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.

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.