Sybase 12.4.2 manual Inserting into primary and foreign key columns, 196

Inserting into primary and foreign key columns

•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.

196