SQL Statements › ALTER TABLE › Processing › Example 6

Example 6

Prevent the entrance of a shipment into the system unless there is an outstanding order for the goods to be shipped.

1. Table containing outstanding orders: ORDERS
2. Table to be protected: CURRENT_SHIPMENTS
3. Columns requiring a match:

   CURRENT_SHIPMENTS columns ORDER_ID and SHIPMENT_NUM to ORDERS columns ORDER_ID and SHIPMENT_ID

4. Constraint name: SHIPMENTS_TO_ORDERS_IDS_MATCH

    EXEC SQL
         ALTER TABLE CURRENT_SHIPMENTS ADD FOREIGN KEY
            (ORDER_ID, SHIPMENT_NUM) REFERENCES
            ORDERS(ORDER_ID, SHIPMENT_ID)
    END-EXEC
   

Note: The columns referenced by a foreign key must correspond to the primary key of the referenced table.