Filter
Top Products
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.