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), 
order_date date NOT NULL, 
fin-code-id char(2), 
region char(7), 
sales_rep integer NOT NULL, 
PRIMARY KEY (id),)

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)