At 07:02 PM 30-04-01 +0100, you wrote:

>Thanks Helen.
>I may be nearly there, see my reply to Geoff. But I am somewhat thrown by
>your (and Jason's )frequent use of the word 'Key' . I am a self taught
>programmer (of data bases, I spent most of my 40 years of programming with
>assembler and embedded instrumentation systems) so I suspect there is a hole
>there in my education somewhere. I supose it stands for 'Key Field'. How
>about a definition of 'Key' for me ?

Think of a key as any column that has a job of linking data between the table it is in and another table. A key should be "atomic", meaning it serves no other purpose than the one it was put there for.

>I know about Primary 'Key' and Foreign 'Key' and that these are fields that
>must hold unique values and are often also an index, is that it ?

Primary and foreign keys are keys. A primary key is put there for the purpose of implementing whatever rules the RDBMS imposes on primary keys. Likewise, a foreign key is put there for the purpose of implementing whatever rules the RDBMS imposes on foreign keys. The concept of "primary" and "foreign" keys is pretty well a generic "given" for a RDBMS, regardless of the way they are implemented. It doesn't necessarily mean that the particular DBMS recognises e.g. a FOREIGN key - in some cases, you have to implement foreign key behaviour and rules yourself. A typical example is Paradox.

btw, foreign keys are not required to be unique. In a well-behaved DBMS like InterBase, primary keys do enforce uniqueness. It doesn't mean that in DBMSs that don't enforce uniqueness, it's OK to have non-unique PKs. It just means that in these cases, the DBMS doesn't give you any help.

The lookup key is just another kind of key. Its atomic function is to point to the source of the lookup data it is "standing in" for. As Geoff has pointed out, InterBase doesn't implement anything to enforce a lookup relationship. Even worse, if you take matters into your own hands and index a lookup key that references a set of control values (Account Type, for example) InterBase will punish you with terrible performance, due to the low selectivity of such an index which causes long duplicate chains to be propagated in the binary index tree.

But a "KeySource-Lookup" relationship between two datasets is a very distinct type of relationship which IBO treats in a way that is quite distinct from master-detail (which it is not). Master-detail in InterBase is implemented by a foreign key in the detail structure referencing the primary key of the master structure.

To further confuse, people coming from desktop dbs often think a key and an index are the same thing. They are not. It so happens that one of the things IB does to implement its rules for a primary key is to create a unique, non-nullable index for its column(s). Currently, it implements its rules for a foreign key by creating a non-unique, non-nullable index for it's column(s). This particular behaviour for foreign keys isn't always desirable - programmers want more control over indexes that are not on primary key columns - so Firebird is looking at making auto-creation of FK indexes optional.

Cheers,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________