SQL Statements › ALTER TABLE › Processing › Example 7

Example 7

Remove the foreign key restriction from table CURRENT_SHIPMENTS in order to ship goods that were mistakenly left out of the original shipments, when the order is no longer current.

• Foreign key name: SHIPMENTS_TO_ORDERS_IDS_MATCH

   EXEC SQL
        ALTER TABLE CURRENT_SHIPMENTS
           DROP FOREIGN KEY SHIPMENTS_TO_ORDERS_IDS_MATCH
   END-EXEC
  

Note: If a domain constraint is added to a table containing rows that violate the constraint, you receive an SQL return code to warn you of this fact. The constraint is added, but the table is placed in "check" status to make it unusable until the problem has been corrected by either running the CONFIRM function of DBUTLTY with the DELETE=YES option specified to delete violating rows, or by dropping the constraint and then running CONFIRM.

A similar situation occurs when adding a foreign key for which the referenced table lacks rows to match all of the values in the referencing table. The same options (previously described) are available to correct the problem. However, in the case of dropping a foreign key to correct the situation, an automatic CONFIRM is initiated for you so that you do not need to perform this step yourself.