Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 180 / 536

Using join indexes
160
Using foreign references
Adaptive Server IQ uses foreign keys to define the relationships among
columns that will be used in join indexes, and to optimize queries. However,
Adaptive Server IQ does not enforce foreign key constraints. For this reason,
when you specify a primary key-foreign key relationship, you must include the
UNENFORCED
keyword.
Adaptive Server IQ does not support key join indexes based on multicolumn
foreign keys.
Examples of join relationships in table definitions
The following example shows how you specify the join relationship by means
of primary and foreign keys. In this case, one customer can have many sales
orders, so there is a one-to-many relationship between the
id
column of the
customer
table (its primary key) and the
cust_id
column of the
sales_order
table. Therefore, you designate
cust_id
in
sales_order
as a
FOREIGN KEY
that
references the
id
column of the
customer
table.
The first example creates the
customer
table, with the column
id
as its primary
key. To simplify the example, other columns are represented here by ellipses
(...).
CREATE TABLE DBA.customer
( id integer NOT NULL,
...
PRIMARY KEY (id),)
Then you create the
sales_order
table with six columns, specifying the column
named
id
as the primary key. You also need to add a foreign key relating the
cust_id
column of the
sales_order
table to the
id
column of the
customer
table.
You can add the foreign key either when you create the table or later. This
example adds the foreign key by including the
REFERENCES
clause as a
column constraint in the
CREATE TABLE
statement.
CREATE TABLE DBA.sales_order
(id integer NOT NULL,
cust_id integer NOT NULL
REFERENCES DBA.customer(id) UNENFORCED,
order_date date NOT NULL,
fin-code-id char(2),
region char(7),
sales_rep integer NOT NULL,
PRIMARY KEY (id),)