CODEWITHSUNDEEP
openedgeprogress-4gl
cagin
cagin

Reputation: 5930

How to get relationship between tables in Progress db

I want to get relationships of a table in progress db. For example:

OrderDetail: Date, Product_Id, Order_Id, Quantity

In this case, I want to get Product_Id and Order_Id columns are Foreign key

Upvotes: 0

Views: 1539

Answers (2)

idspispopd
idspispopd

Reputation: 404

While the OpenEdge database and the ABL engine don't know about relationships or external keys, the SQL engine does implement foreign key constraints. See https://knowledgebase.progress.com/articles/Article/000034195

I don't know if this is useful for you. These constraints would be to be defined first if they don't exist already, which is unlikely if your application is mainly ABL and not SQL. Also the website would need to access the database through SQL. It is not enough to write SQL statements in your ABL code, the access needs to go through the SQL engine.

Upvotes: 0

Tom Bascom
Tom Bascom

Reputation: 14020

The OpenEdge database does not have explicit support for "foreign keys".

Some application schemas have naming conventions that might help you.

You can, as Mike mentioned, loop through the meta schema tables _file, _field and _index and apply logic that follows a such a naming convention but there is no generic solution that can be applied to all OpenEdge databases.

For instance, if you naming convention is that a field name of tableNameId indicates a potential foreign key for tableName you might try something like:

find _file no-lock where _file._file-name = "tableName" no-error.
if available( _file ) then
  do:

    find _field no-lock where _file-recid = recid ( _file ) and _field-name = "tableNameId" no-error.

    if available( _field ) then
      do:

        message "common field exists!".

        find first _index-field no-lock where _field-recid = recid( _field ) no-error.
        if available( _index-field ) then
          do:

            message "and there is at least one index on tableNameId!".

            find _index no-lock where recid( _index ) = _index-recid no-error.

            message _index-name _unique _num-comp.  /* you probably want a unique single component index */

          end.

      end.

  end.

Upvotes: 5

Related Questions