Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 181 / 536

CHAPTER 4 Adaptive Server IQ Indexes
161
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