Sybase 12.4.2 Using foreign references, Examples of join relationships in table definitions, 160

Using join indexes

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

160