Examples of join relationships in table definitions

The following example shows how to 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 Customers table (its primary key) and the CustomerID column of the SalesOrders table. Therefore, designate CustomerID in SalesOrders as a FOREIGN KEY that references the ID column of the Customers table.

The first example creates the Customers table, with the column ID as its primary key. To simplify the example, other columns are represented here by ellipses (...).

CREATE TABLE GROUPO.Customers
( ID INTEGER NOT NULL,
... 
PRIMARY KEY (ID),)

Next, create the SalesOrders table with six columns, specifying the column named CustomerID as the primary key. You must also add a foreign key relating the CustomerID column of the SalesOrders table to the ID column of the Customers 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 GROUPO.MySalesOrders
(ID INTEGER NOT NULL,
CustomerID INTEGER 
REFERENCES GROUPO.Customers(ID), 
OrderDate DATE NOT NULL, 
FinancialCodesID CHAR(2), 
Region CHAR(7), 
SalesRep INTEGER NOT NULL, 
PRIMARY KEY (ID),)

Alternatively, you can create the table without the REFERENCES clause, and then add the foreign key later, as is done in the following ALTER TABLE statement. You may issue one or the other of these statements, but not both:

ALTER TABLE GROUPO.MySalesOrders
ADD FOREIGN KEY ky_so_customer (CustomerID) 
REFERENCES GROUPO.Customers (ID)