Cascade actions

[palthe]

I've challenged myself to do the following:
I want to perform cascade actions between table contents, in a dynamic way.

E.g.:
If I have record x in table A, and I know that there are records y in table B directly related to record x, it's easy.
I can, for example, do a join to delete the records in both tables.

But now the problem: what if I DON'T know the relationships from table A to all the other tables in the database, but still want to perform a cascade delete?

AFAIK, Progress has no foreign keys, so the relationship between tables is not registered in the database itself. Or is it? I'm now experimenting with the use of the primary unique index, compared to indexes of other tables.
This gives me somewhat of a result, but not exactly the result I want (that is, all the directly related tables to the table on which I want to perform some cascade action).

I know Progress has it's routines for this because you can find the table relationships in the Data Administration (what's the function called again, something like Query/Results?).

Anyone familiar with this problem?

 

[Casper]

Hi Palthe,

The table relations found through the data adminstration don't need to be correct. The relations are made because of the assumption (naming convention) that (index) fields with the same name are the same fields.
So this can be pretty dangerous if you don't use your fieldnames consistently.
To name a worse case scenario:
You name all the fields in a table with letters from the alfabet, starting with 'a'.
If a field, 'a' for example would show up in an index of any table it would imply they have a relationship with each other, but that isn't correct.
You have to be absolutely sure that the naming convention you use in your database is been consistently what progress assumes it is otherwise you get yourself into a lot of trouble doing this dynamically.

The 'referential integrity' of Progress databases is handled via the database triggers (write/create/delete) and i suppose that is the best way to keep it, otherwise you must make the rules yourself. (which also happens a lot)

But doing this dynamcially sounds dangerous to me.

We for example have foreign keys in our database with an entirely different name then the corresponding key..

But imagine that instead of not finding a relationship, you will find a wrong relationship.... ouch....

Casper.

 

[palthe]

Hi Casper,

thanks for the answer. Yes, that was what I was afraid of...

I do the same thing with my own code, I extract the primary key from the table and I compare it with the indexes of the other tables (in this case 700+ tables). And indeed, I sometimes get the wrong table relationship through this method.
And you just made a point that I'm very aware of:
sometimes missing a table with referential material isn't a show stopper. But deleting data from a table which "seems" to have references to the original table, but actually hasn't... Well, let's just say, that's a no-go .

I didn't know that Progress itself does the same thing within DA, that is not a trustworthy method at all!

So, basically, the answer is no; you can't extract table references dynamically through 4GL and that about sums it up?

Thanks!

 

[Casper]

So, basically, the answer is no; you can't extract table references dynamically through 4GL and that about sums it up?

In short: yes.

Referential integrity is one of the things many AP asked Progress to do. But as for now, it is still on the wish list.
You can imagine that it would be quite a big task to implement this efficiently and still have to make sure that the current situation also keeps working.

Casper.