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