Chris Breemer ([log in to unmask]) wrote:
: Hi All,
 
: I have a question for the Allbase gurus among you.
 
: It says in the Allbase DBA guide (chapter 'Logical Design', section
: 'Determining Index Keys') that you must create an index on a primary key.
: What happens if you don't do this ? Our Allbase driver has never done this
: and seemed to work well (albeit slowly, which is perhaps explained by the
: absence of the required index). Does it make sense to have a primary key
: also
: be an index too? I would say a pk should be an index automatically.
 
: To complicate things, it seems that we have been advised by HP in the past
: NOT
: to create an index on a pk, which is why we omitted it in the driver. I
: seem
: to remember that at that time (I think it was Allbase E.0) the PRIMARY KEY
: clause did not actually do anything. Does it now (Allbase F.0, G.0) ?
 
: I hope somebody can shed some light on this obscure topic.
 
Chris,
 
I believe the problem is one of semantics - "primary key" is both a logical
database concept and an implemented feature in ALLBASE/SQL.  I think the
verbage in the DBA guide was written before constraints were implemented,
and refers to the logical database concept.
 
C.J. Date writes the following in "An Introduction to Database Systems,
Volume II": "By definition, the primary key of every relation in the
database (a) has the property of uniqueness - no two tuples in the relation
can have the same primary key value at the same time - and (b) cannot
accept null values." In this use of the word, "primary key" is a logical
concept.
 
Prior to E.0, ALLBASE did not support the PRIMARY KEY clause in the
CREATE TABLE syntax.  So, to make the "primary key" on your table behave
like a "primary key" is supposed to behave, you needed to create a unique
index yourself.  When you use the CREATE INDEX command, you create a
b-tree structure (which "enforces" the constraint) that is visible in
SYSTEM.INDEX.
 
When the PRIMARY KEY clause was implemented, a side-effect of using it is
that a b-tree structure is automatically created for you that is visible
in SYSTEM.CONSTRAINTINDEX.  Because the b-tree structure to enforce the
"primary key" definition has already been created, you don't need to
issue a CREATE INDEX command.  If you do, you waste disk space for the
extra b-tree structure, and you also slow performance when inserts/updates/
deletes occur that impact the b-tree structure (because they must be
made to both b-trees).  This is true for E.0 and all later releases.
 
I've been in the HP database lab for a long time (over 7 years) and am not
aware of a single case where we have implemented a clause that didn't do
anything.  Such a clause would be viewed as "vaporware" around here.
 
Hope this helps,
Leslie-Anne