Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 216 / 536

Inserting into primary and foreign key columns
196
• The second user's transaction begins before the first user's transaction
commits.
• The second user tries to update after the first user's transaction commits,
but before the join index is synchronized.
This problem occurs because Adaptive Server IQ makes a new version of the
join index when any of its underlying tables is updated. The new version is not
visible to other transactions that have already begun. The problem does not
occur when one user makes all of the changes, because the newer table version
is visible to the user who made the original changes.
For example, assume that tables A, B, and C are all members of the same join
index. User 2 begins a transaction, and writes to another table not involved in
the join. Now, User 1 inserts into table B. This action creates a new version of
table B, and a new version of the join index. User 2 then tries to write to table
C. Even though no other user has changed table C, because C is a member of
the join index it can't be updated until the join index is synchronized.
For more information on join indexes, see Chapter 4, “Adaptive Server IQ
Indexes” For more information on transaction processing, see Chapter 8,
“Transactions and Versioning”
Inserting into primary and foreign key columns
You load or insert data into primary key and foreign key columns just as you
would into any other column.
When you insert into a single-column primary key, Adaptive Server IQ checks
that each value is unique. If it is not, an error occurs.
When you insert into multi-column primary keys, you are responsible for
making sure that the values in the primary key columns uniquely identify each
row. Adaptive Server IQ does not enforce multi-column primary key
uniqueness.
When you insert into foreign key columns, you are responsible for making sure
that the values match those in the column they reference. Adaptive Server IQ
does not enforce foreign keys. For example, in the sample database, the
cust_id
column in the
sales_order
table is a foreign key that references the
id
column
in the
customer
table. You must insert values directly into both of these
columns, and ensure that they match.