Sybase 12.4.2 manual Modifying tables included in a join index, 167

CHAPTER 4 Adaptive Server IQ Indexes

SELECT sales_rep FROM sales_order

WHERE sales_rep = 299

the results show 20 rows with 299 in the sales_rep column.

However, if you enter:

SELECT emp_id FROM employee

WHERE emp_id = 299

the results show only one row with 299 in the emp_id column.

Note Query optimizations for star joins rely on the underlying primary key- foreign key relationships. Because Adaptive Server IQ does not enforce foreign keys, in order for your query results to be exactly as expected, your application needs to ensure that data inserted into or deleted from the database does not violate the primary key-foreign key relationship.

To declare a foreign key, see “Creating primary and foreign keys” on page 125. For other information on foreign keys, see “Declaring entity and referential integrity” on page 281.

Modifying tables included in a join index

Once you have created a join index, you are restricted in the types of changes you can make to the join index and its underlying tables and indexes.

You cannot drop any table that participates in a join index. Likewise, you cannot use ALTER TABLE to add, drop, or modify a column that participates in a join index. In both cases, you must first drop the join index. Then you can either drop the table, or modify any columns that participate in the join index.

You can add columns to the tables that participate in a join index. However, there are restrictions on inserting data into these columns, as described in the next section.

You can drop indexes on columns not involved in the join relationship, and you can add, drop or modify nonjoined columns of tables in a join index. However, you cannot drop either the indexes on a join column or the join column itself. You need at least one index on a column involved in a predefined join relationship. It is highly desirable to have either an HG or LF index on all columns that are part of a join index.

167