Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 302 / 536

Declaring entity and referential integrity
282
Enforcing entity integrity
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.
Example 1 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.
Example 2 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.