Sybase 12.4.2 manual Inserting or deleting from tables in a join index, 168

Using join indexes

Adaptive Server IQ automatically applies the changes to the join index at the same time as it changes the base table. You do not need to synchronize the join index after any ALTER TABLE on nonjoined columns.

Other restrictions on ALTER TABLE for join indexes include the following:

•You cannot rename a column into or out of a NATURAL join condition.

•You cannot add a column that would participate in a previously specified

NATURAL join.

•You cannot drop a PRIMARY KEY/FOREIGN KEY relationship if it matches a join condition that is in use in a join index.

•You cannot drop a NOT NULL constraint from a column that participates in a join condition.

•You cannot modify the data type of a column that participates in a join condition.

Inserting or deleting from tables in a join index

You always insert or load into, or delete from, the underlying tables, not the join index itself. When you first create the join index, Adaptive Server IQ synchronizes the joined tables automatically, whether or not you have previously loaded data into the tables.

If you insert into or delete from a table that participates in an existing join index, you must synchronize the join index explicitly, unless you are updating the top table in the join hierarchy. If you insert rows and then delete them before the synchronization takes place, Adaptive Server IQ optimizes synchronization to omit the insertions.

You cannot perform partial-width inserts to tables that participate in a join index. If you need to add columns to a table in a join index, you must do one of the following:

•Drop the join index, do the partial-width insert, and then recreate the join index.

•Load or insert into all columns of the table.

168