Sybase 12.4.2 manual Specifying the join type when creating a join index, 161

CHAPTER 4 Adaptive Server IQ Indexes

Alternatively, you could create the table without the REFERENCES clause, and then add the foreign key later, as is done in the following ALTER TABLE statement:

ALTER TABLE DBA.sales_order

ADD FOREIGN KEY ky_so_customer (cust_id)

REFERENCES DBA.customer (id) UNENFORCED

Specifying the join type when creating a join index

The join type is always FULL OUTER, the keyword OUTER being optional. You also need to do one of the following:

•If you are joining equivalent columns with the same name from two tables, you specify that it is a NATURAL JOIN.

•If you are joining columns based on keys, you must also have specified the

relationship in the underlying tables as a FOREIGN KEY that references a

PRIMARY KEY.

•If you are joining equivalent values (an equijoin) in columns from two tables, you specify an ON clause.

These rules conform to ANSI syntax requirements.

Specifying relationships when creating a join index

For non-key joins, the order in which you specify tables when you create the join index determines the hierarchy of the join relationship between the tables. The CREATE JOIN INDEX statement supports two ways to specify the join hierarchy:

•List each table starting with the lowest one in the hierarchy, and spell out the join relationship between each pair of tables. The last table in the list will be the top table in the hierarchy. For example, in Figure 4-1 on page 153, F is the top table, E is below it, and C is at the bottom of the hierarchy. You could specify the join hierarchy for these three tables as follows:

C FULL OUTER JOIN E FULL OUTER JOIN F

161