Referential (foreign key) constraints are a required feature of the SQL standard. They prevent operations from inserting a record referencing a record in another table that does not exist, deleting a record which is referenced by a record in another table, or updating a record of either table in such a way that the integrity of the relationship is violated. Referential constraints are implemented by the OpenEdge SQL engine, which runs as a separate process (_sqlsvr2) in addition to the main database process (_mprosrv) when a SQL server is started on the database.
Referential constraints are specified in CREATE TABLE or ALTER TABLE statements. In the following example, Item_No is a foreign key referencing the Item table. The REFERENCES keyword specifies that for any record in the Supplier_Item table, there must be a record in the Item table with an Item_No field containing the same value:
CREATE TABLE Supplier_Item
Suppl_No INTEGER NOT NULL PRIMARY KEY,
Item_No INTEGER REFERENCES Item (Item_No),
Foreign key support is not implemented in the base OpenEdge database engine or in the OpenEdge ABL. Referential integrity is typically enforced in ABL by the use of database triggers. Database triggers are ABL procedures that are associated in the data dictionary (schema triggers) or in application code (session triggers) with a database event (CREATE, DELETE, FIND or WRITE of a table record, or ASSIGN of a field). When the event occurs, the trigger code is run automatically.
In the following example of a DELETE trigger, the trigger checks for Order records with the same CustNum value when an attempt is made to delete a Customer record. If any such Order records are found, the trigger code returns an error and the Customer record is not deleted. This preserves referential integrity between the Customer and Order tables.
FIND FIRST Order OF Customer NO-ERROR.
IF NOT AVAILABLE Order THEN DO:
MESSAGE "Open orders exist for Customer " Customer.CustNum ". Cannot delete." VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.