Article

Is there a way to create a foreign key on the database level to ensure referential integrity for ABL clients ?

« Go Back

Information

 
Article Number000034195
EnvironmentProduct: OpenEdge
Version: All supported versions
OS: All supported platforms
Question/Problem Description
Is there a way to create a foreign key on the database level to ensure referential integrity?

Does the OpenEdge database support foreign keys for ABL clients?

How does OpenEdge SQL implement referential integrity / foreign key constraints? Why is the same functionality not available to ABL clients?
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
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),
Quantity INTEGER
) ;
 
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:
RETURN.
END.   
ELSE DO:
MESSAGE "Open orders exist for Customer " Customer.CustNum  ".  Cannot delete." VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
RETURN ERROR.
END.
Workaround
Notes
References to Other Documentation:

OpenEdge Data Management: SQL Development, "OpenEdge SQL Data Definition Language : Maintaining data integrity : Referential constraints"
https://documentation.progress.com/output/OpenEdge116/openedge116/#page/dmsdv/referential-constraints.html

OpenEdge Development: ABL Database Triggers and Indexes, "Database triggers"
https://documentation.progress.com/output/OpenEdge116/openedge116/#page/wp-abl-triggers/database-triggers.html
Attachment 
Last Modified Date12/21/2016 11:00 PM


Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025