Sybase 12.4.2 Enforcing entity integrity, If a client application breaches entity integrity, 282

Declaring entity and referential integrity

Enforcing entity integrity

Example 1

Example 2

When a new row in a table is created, or when a row 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.

Note Adaptive Server IQ enforces single-column primary keys only. No action is taken for invalid multi-column primary keys. If you have any multi-column primary keys, you may want to define a procedure to use when you load or insert data, that validates each set of values you insert in the primary key columns.

You cannot create a join index that relies on a foreign key-primary key relationship where the primary key is multi-column.

The employee table in the sample database uses an employee ID as the primary key. When a new employee is added to the table, IQ 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.

Because it relies on multiple columns, this primary key is unenforced in the current version of Adaptive Server IQ. However, you could create a stored procedure to check insertions in both columns.

If a client application breaches entity integrity

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 single-column primary key value, and provides values that are not unique, entity integrity would be breached.

If an attempt to breach entity integrity is detected, Adaptive Server IQ does not add the new information to the database, and instead reports an error to the client application.

282