Subject Re: [IBO] Problem with KeyLinks
Author Salvatore Besso
hello Helen,

starting from bottom :-)

> Gosh, I seem to have been writing variations to this stuff
> for a week or more. Why don't you guys read other people's
> posts?

Sorry but I don't keep all messages of the list and maybe that messages have
been already deleted.

> Right, for *any* dataset, they are a list of database columns,
> that are all present in the query's output set, that form a
> unique key for the set. For single table queries, that should
> be the primary key;

Exactly, ID_ANAGRAPHIC (and ID_PROVINCE as well) is unique since it is retrieved
from a generator. Instead TX_PROVINCE and OWN_PROVINCE in ANAGRAPHICS are
foreign keys pointing to the PK of PROVINCES (ID_PROVINCE).

> although if your table is not very well normalised, it could be
> another unique column or column set from the table.

Maybe this could the case?

> Then, for lookup datasets, to implement the tib_lookupcombo, Keylinks
> does double duty. It must be the unique key of the lookup set but it
> is ALSO used to link that unique key to the column in the parent set
> (or, more correctly, the "Keysource dataset") from which the lookup is
> being polled.

That's what I have done. The lookup combos in the form work as expected and the
column in the parent table is updated accordingly.

> Also, the first version is not "non-standard for SQL-92" - it is
> perfectly standard.

Somewhere I had read that it should be a good practice to use joins instead of
sub-selects, but I can survive with them.

> Your problem with the second query is that you have a join there. This
> "flattens" the output set and potentially gives you multiple rows with the

Sorry to be obvious, but my knowledge of SQL + IBO stuff is still very poor, and
I suppose that all of us have passed through this stage :-) so I still wonder
how it could, since ID_ANAGRAPHIC is unique and two rows couldn't exist in

But, as already said, I can leave the query with sub-selects if it is standard
as you've said.

> 2. Write executable stored procedures to perform updates, inserts and
> deletes to multiple tables, as required.

No, updates, inserts and deletes are made only on ANAGRAPHICS. PROVINCES is only
a lookup table and it is not touched during the updates, inserts and deletes
that are single table operations.

Maybe I forgot to say that no foreign keys between ANAGRAPHICS and PROVINCES
exist actually. Normally it wouldn't be a good idea and I agree, because if a
province is deleted the row into ANAGRAPHICS will point to a non-existent row.
But I've said normally, because in my case the user has no access to the
PROVINCES table for any modification. The table is already populated with data
and can only be read. For this reason there are no FK indexes.