How to add a FOREIGN KEY to an existing table?

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 [:O]
You made my day! [:D]  "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?

[mention:56f379ec5a8e40bfb5562782e109a700:e9ed411860ed4f2ba0265705b8793d05], 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.

[quote user="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.

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.

Primary index --- is the unfortunate naming. It sounds like a Primary Key (PK), but it has nothing to do with a SQL PK as Jyothi wrote above.

I believe it should have been called "First index" instead of Primary index.

SQL PK is quite different -- it is a "constraint on uniqueness", supported by a unique index. But also it is a primary unique constraint. A table could have many unique constraints. And only one PK.

The term "Primary Index" in the 4GL predates the first SQL standard.

It was originally intended to be the index that was used for 4GL queries as the "default index", used when there was no other information, such as the terms of a where clause, that could be used to choose another more suitable index.

As dmitri says, it has nothing to do with primary keys.