Browse Prior Art Database

Automatic Creation And Dropping of Primary Indexes

IP.com Disclosure Number: IPCOM000119819D
Original Publication Date: 1991-Mar-01
Included in the Prior Art Database: 2005-Apr-02
Document File: 2 page(s) / 100K

Publishing Venue

IBM

Related People

Boykin, JR: AUTHOR [+3]

Abstract

The design for a program is disclosed allowing a user of a Relational Database Management System to define a Primary Key for a new or existing table in such a way that is consistent with the relational model while optimally providing for ease of use, performance, and compatibility with existing data and systems.

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 52% of the total text.

Automatic Creation And Dropping of Primary Indexes

      The design for a program is disclosed allowing a user of
a Relational Database Management System to define a Primary Key for a
new or existing table in such a way that is consistent with the
relational model while optimally providing for ease of use,
performance, and compatibility with existing data and systems.

      A Primary Key is a set of columns in a table that must contain
unique non-null values.  The requirement for non-null values is
enforced by the Database Manager as any other column defined with the
NOT NULL attribute.  The uniqueness of the column values can be
enforced by a unique index on the columns in the primary key.  An
index that enforces the uniqueness of a primary key is called a
Primary Index.

      A Primary Key can be defined for a table in one of two ways:
1.   When the table is created via syntax in the CREATE
      TABLE statement.  For example:
      CREATE TABLE EMP (EMPNBR INT NOT NULL PRIMARY KEY, ...)
2.   By altering an existing table via syntax in the ALTER
      TABLE statement.  For example:
           ALTER TABLE EMP PRIMARY KEY (EMPNBR)

      From an ease-of-use perspective, it is highly desirable to
automatically create the primary index when the primary key is
defined.  Otherwise, the user must perform an extra step when
defining a primary key.  From a compatibility perspective, it is also
desirable to recognize a unique index already exists that fits the
requirements for the primary index (this obviously only applies to
the case where the table is being altered).  Otherwise, the user must
either drop their existing index when altering the table to define
the primary key, or the table will have a redundant index, with a
subsequent negative impact to performance on subsequent modifications
to the table.  However, the dropping and recreating of an index also
has a negative performance implication.

      Since the user must be prevented from dropping a Primary Index,
recognizing an existing index during the altering of the table when a
primary key is defined and using it as the Primary Index could create
an asymmetrical situation if the primary key is dropped.  When the
primary key is dropped, the Database Manager would need to remember
the original situation to avoid asymmetry.

      OS/2* EE Database Manager provides an optimal solution to the
user.  When the user creates a table and defines a primary key, the
Database Manager automatically creates a Primary Index, freeing the
us...