How to add a FOREIGN KEY to an existing table? - Forum - OpenEdge RDBMS - Progress Community

How to add a FOREIGN KEY to an existing table?

 Forum

How to add a FOREIGN KEY to an existing table?

This question is answered

Hello,

I am trying to add a foreign key to my existing table using by SQL command.

ALTER TABLE PUB."Order" ADD CONSTRAINT ORDER_CUSTOMER_FK FOREIGN KEY ("Cust_Num") REFERENCES PUB."Customer"("Cust_Num");

I do it in the Dbeaver. But I got the following error:

SQL Error [HY000]: [DataDirect][OpenEdge JDBC Driver][OpenEdge] No matching key defined for the referenced table (7545)

The ProKb says about this error that:

Cause
         Columns defining a primary or foreign key must not accept the NULL value.
Resolution
         Make sure that the table columns chosen as primary or foreign key do not accept NULL values by making them mandatory.
         In a .df file a line mentioning:
         MANDATORY
         should be visible for those columns.

These are the data definitions for my two test tables. I wanna to create a foreign key between Order.Cust_Num and Customer.Cust_Num fields. Both fields have the MANDATORY option:

ADD TABLE "Customer"
AREA "Customer"
DUMP-NAME "customer"

ADD FIELD "Cust_Num" OF "Customer" AS character
FORMAT "x(8)"
INITIAL ""
POSITION 2
MAX-WIDTH 20
ORDER 10
MANDATORY

ADD FIELD "Name" OF "Customer" AS character
FORMAT "x(20)"
INITIAL ""
POSITION 3
MAX-WIDTH 20
ORDER 20

ADD INDEX "CustNumIdx" ON "Customer"
AREA "Customer"
UNIQUE
PRIMARY
INDEX-FIELD "Cust_Num" ASCENDING

ADD TABLE "Order"
AREA "Order"
DUMP-NAME "order"

ADD FIELD "Order_Num" OF "Order" AS character
FORMAT "x(8)"
INITIAL ""
POSITION 2
MAX-WIDTH 20
ORDER 10
MANDATORY

ADD FIELD "Cust_Num" OF "Order" AS character
FORMAT "x(8)"
INITIAL ""
POSITION 3
MAX-WIDTH 20
ORDER 20
MANDATORY

ADD INDEX "OrderNumIdx" ON "Order"
AREA "OrderIDX"
UNIQUE
PRIMARY
INDEX-FIELD "Order_Num" ASCENDING

ADD INDEX "CustNumIdx" ON "Order"
AREA "Customer"
INDEX-FIELD "Cust_Num" ASCENDING

Please advise me what I'm doing wrong?

Verified Answer
  • , You are right that we can have foreign keys at SQL level and the article describes the same.

    I tired the steps mentioned by you in the first post and I am facing the same error that you see: SQL Error [HY000]: [DataDirect][OpenEdge JDBC Driver][OpenEdge] No matching key defined for the referenced table (7545)

    I also tried after some trivial changes to .df files like changing INITIAL from "" to "?" but nothing worked.

    Then I tried creating the same tables from SQL with "not null primary key" constraint for "cust_num" column while creating tables. This way, adding a foreign key constraint worked without any errors.

    So, there is some difference between creating tables from ABL/Dictionary and SQL which is causing the errror.

  • Adding a primary key to Customer table solves the problem.

    Syntax to create Primary Key is :

    ALTER TABLE PUB."Customer" ADD CONSTRAINT CUSTOMER_PK PRIMARY KEY ("Cust_Num");

All Replies
  • I am using OpenEdge Release 11.7.4 on Win64.

  • @YerAmil, Just a warning, the base OE database engine doesn't have FK constraints.  It is a relational database without relations.  If I had to guess, it probably will never get them.

    I'm not familiar with adding FK constraints that are enforced in the SQL engine, but there is a KB.  You are defining your datadefinitions using the old DF format, but you probably need to do it using an ODBC/JDBC connection to the SQL engine.  You can do that with PDSOE or the sqlexp tool, for example.  Here is the KB about defining FK's that only impact SQL clients.

    knowledgebase.progress.com/.../000034195

    I'd suggest you vote for this too :

    community.progress.com/.../make_the_progress_openedge_database_a_relational_database

  • Thank you @dbeavon!

    >>It is a relational database without relations.

    I'm shocked Surprise
    You made my day! Big Smile  "a relational database without relations" - I have to remember this one, it's funny.

    I'll tell my colleagues in Oracle about it, they should be laughing too )))

    >>I'd suggest you vote for this too :

    It seems to me that the vote for this is closed.  This vote was opened five years ago, and they still don't have it.

  • I'd think dbeavon meant something like "relational database without *relationships*".

    In the relational model a relation is the equivalent of a table and OE DB surely has tables, hence a relational database.

  • @kirchner good point (and very technical).

    I would also include the word "constraint", since the DBMS should explicitly enforce the FK.

    (Progress may end up implementing something called "Referential Association", and they may use the term "relationship" for that, even if it isn't a constraint enforced by the DBMS.).

  • Ok. Thank you all for answers.

    But I am little confused.

    Judging by this article, this is possible, but only need to be sure that the table columns chosen as primary or foreign key do not accept NULL values by making them mandatory. There also use the PUB scheme in the example.

    knowledgebase.progress.com/.../SQL-command-to-add-a-FOREIGN-KEY-constraint-to-an-existing-table-fails-with-the-error-7545

    From this article I conclude that it is still possible at the SQL level. Am I wrong again?  If I'm wrong, what is this article for?  It is a bug? Where it is a bug, in the article or in my code above?

    Ok. Suppose the use of FK is not possible in OpenEdge at the SQL level.

    At the OpenEdge ABL level to implement something like referential integrity I have to write an ABL procedure for the trigger to delete, create, etc. Right?

    But it is inconvenient for database exploitation:

    1. The ABL trigger is not part of the database and must be stored separately in the file system.

    2. The ABL code of the trigger can be illegally replaced by someone. Then we will not know that this code has been changed. Is'n it?

    3. From ABL, someone can programmatically disable triggers using the DISABLE TRIGGERS statement. Right?

    4. ABL triggers do not cover SQL operations. Right?  How to ensure referential integrity at the SQL level in OpenEdge?

  • , You are right that we can have foreign keys at SQL level and the article describes the same.

    I tired the steps mentioned by you in the first post and I am facing the same error that you see: SQL Error [HY000]: [DataDirect][OpenEdge JDBC Driver][OpenEdge] No matching key defined for the referenced table (7545)

    I also tried after some trivial changes to .df files like changing INITIAL from "" to "?" but nothing worked.

    Then I tried creating the same tables from SQL with "not null primary key" constraint for "cust_num" column while creating tables. This way, adding a foreign key constraint worked without any errors.

    So, there is some difference between creating tables from ABL/Dictionary and SQL which is causing the errror.

  • Dileep Dasa

    Then I tried creating the same tables from SQL with "not null primary key" constraint for "cust_num" column while creating tables. This way, adding a foreign key constraint worked without any errors.

    So, there is some difference between creating tables from ABL/Dictionary and SQL which is causing the errror.

    Well, it turns out it's not that bad.

    Thank you very much, Dileep Dasa! 

    I will try it.

  • Adding a primary key to Customer table solves the problem.

    Syntax to create Primary Key is :

    ALTER TABLE PUB."Customer" ADD CONSTRAINT CUSTOMER_PK PRIMARY KEY ("Cust_Num");

  • He already have such an index in the customer table:

    ADD INDEX "CustNumIdx" ON "Customer"

    AREA "Customer"

    UNIQUE

    PRIMARY

    INDEX-FIELD "Cust_Num" ASCENDING

    Or is the Primary Key here something different (OE vs SQL) and it is necessary to add it at the SQL level?

  • The 4GL requires a primary index for every table. This primary index doesn't have to be unique. The Progress Sql Primary Key constraint is something you tag per column and is optional. A Sql Primary Key constraint is a "constraint on uniqueness", but isn't equal to an index.

  • Thank you! I'll try it too.