Sybase 12.4.2 User Manual

Page 180

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

column of the

customer

table (its primary key) and the

cust_id

column of the

sales_order

table. Therefore, you designate

cust_id

sales_order

as a

FOREIGN KEY

that

references the

column of the

customer

table.

The first example creates the

customer

table, with the column

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

as the primary key. You also need to add a foreign key relating the

cust_id

column of the

sales_order

table to the

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),)