Filter
Top Products
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