Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 4: Modifying Data

Home Contents Index Master Index New Book

Data Integrity

The INSERT, UPDATE, and DELETE statements modify data in an existing database. Whenever you modify existing data, the integrity of the data can be affected. For example, an order for a nonexistent product could be entered into the orders table, a customer with outstanding orders could be deleted from the customer table, or the order number could be updated in the orders table and not in the items table. In each of these cases, the integrity of the stored data is lost.

Data integrity is actually made up of the following parts:

Entity integrity. Each row of a table has a unique identifier.
Semantic integrity. The data in the columns properly reflects the types of information the column was designed to hold.
Referential integrity. The relationships between tables are enforced.

Well-designed databases incorporate these principles so that when you modify data, the database itself prevents you from doing anything that might harm the data integrity.

Entity Integrity

An entity is any person, place, or thing to be recorded in a database. Each entity represents a table, and each row of a table represents an instance of that entity. For example, if order is an entity, the orders table represents the idea of order and each row in the table represents a specific order.

To identify each row in a table, the table must have a primary key. The primary key is a unique value that identifies each row. This requirement is called the entity integrity constraint.

For example, the orders table primary key is order_num. The order_num column holds a unique system-generated order number for each row in the table. To access a row of data in the orders table, you can use the following SELECT statement:

SELECT * FROM orders WHERE order_num = 1001

Using the order number in the WHERE clause of this statement enables you to access a row easily because the order number uniquely identifies that row. If the table allowed duplicate order numbers, it would be almost impossible to access one single row, because all other columns of this table allow duplicate values.

For more information on primary keys and entity integrity, refer to Chapter 8, "Building Your Data Model."

Semantic Integrity

Semantic integrity ensures that data entered into a row reflects an allowable value for that row. The value must be within the column-specific properties, or allowable set of values, for that column. For example, the quantity column of the items table permits only numbers. If a value outside the column-specific properties can be entered into a column, the semantic integrity of the data is violated.

Semantic integrity is enforced using the following constraints:

Data type. The data type defines the types of values that you can store in a column. For example, the data type SMALLINT allows you to enter values from -32,767 to 32,767 into a column.
Default value. The default value is the value inserted into the column when an explicit value is not specified. For example, the user_id column of the cust_calls table defaults to the login name of the user if no name is entered.
Check constraint. The check constraint specifies conditions on data inserted into a column. Each row inserted into a table must meet these conditions. For example, the quantity column of the items table might check for quantities greater than or equal to one.

For more information on using semantic integrity constraints in database design, refer to "Defining Column-Specific Properties".

Referential Integrity

Referential integrity refers to the relationship between tables. Because each table in a database must have a primary key, this primary key can appear in other tables because of its relationship to data within those tables. When a primary key from one table appears in another table, it is called a foreign key.

Foreign keys join tables and establish dependencies between tables. Tables can form a hierarchy of dependencies in such a way that if you change or delete a row in one table, you destroy the meaning of rows in other tables. For example, Figure 4-1 shows that the customer_num column of the customer table is a primary key for that table and a foreign key in the orders and cust_call tables. Customer number 106, George Watson, is referenced in both the orders and cust_calls tables. If customer 106 is deleted from the customer table, the link between the three tables and this particular customer is destroyed.

When you delete a row that contains a primary key or update it with a different primary key, you destroy the meaning of any rows that contain that value as a foreign key. Referential integrity is the logical dependency of a foreign key on a primary key. The integrity of a row that contains a foreign key depends on the integrity of the row that it references-the row that contains the matching primary key.

By default, the database server does not allow you to violate referential integrity and gives you an error message if you attempt to delete rows from the parent table before you delete rows from the child table. You can, however, use the ON DELETE CASCADE option to cause deletes from a parent table to trip deletes on child tables. See "Using the ON DELETE CASCADE Option".

Figure 4-1
Referential Integrity in the stores7 Database

To define primary and foreign keys, and the relationship between them, use the CREATE TABLE and ALTER TABLE statements. For more information on these statements, see Chapter 1 of the Informix Guide to SQL: Syntax. For information on building data models using primary and foreign keys, refer to Chapter 8, "Building Your Data Model."

CREATE TABLE accounts (

 acc_num SERIAL primary key,

 acc_type INT,

 acc_descr CHAR(20));



CREATE TABLE sub_accounts (

 sub_acc INTEGER primary key,

 ref_num INTEGER REFERENCES references accounts (acc_num) ON DELETE CASCADE,

 sub_descr CHAR(20));